vba for find last column with hidden columns in worksheet

Hello, 

This code has worked for finding the last column:
lc = ws.cells(7, columns.count).end(xltoleft).column.

However,  when I used this code for a sheet that has hidden columns it would 
not work.

What would be the correct code in this instance?

Please assist.

Thanks from OffDev wannabe
0
Utf
12/1/2009 11:31:02 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2551 Views

Similar Articles

[PageSpeed] 20

Hi,

lc = Rows(7).Find(What:="*", SearchDirection:=xlPrevious).Column

Mike

"OffDev" wrote:

> Hello, 
> 
> This code has worked for finding the last column:
> lc = ws.cells(7, columns.count).end(xltoleft).column.
> 
> However,  when I used this code for a sheet that has hidden columns it would 
> not work.
> 
> What would be the correct code in this instance?
> 
> Please assist.
> 
> Thanks from OffDev wannabe
0
Utf
12/2/2009 12:47:01 AM
Reply:

Similar Artilces:

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Paste into "Advanced Find" "search for the words:" field????
Am I doing something wrong, or did microsoft really disable the ability to paste into the "search for the words:" field? You can paste into the email address fields, but that is it. I'm trying to search emails for a specific numeric string, and I don't want to have to type a 30 character number from an email when I could easily copy and paste it. Am I doing something wrong or are they really that stupid??? Thanks! Mick Does CTRL V work for you "Mick" <MickBurkellc@gmail.com> wrote in message news:1121234980.931014.46200@z14g2000cwz.googlegroups.com... >...

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Excel/VBA
Help!!! Excel/VBA The need: To collapse a spreadsheet by deleting irrelevant rows conditioned on a cell in the row. Question 1: Is there an intrinsic way of doing this built into Excel? Question 2: I am trying to do it with VBA by cutting and pasting rows as follows : ActiveSheet.Rows("(SrcRow)").Cut ActiveSheet.Rows("(DsrRow)").Paste I keep getting “Type mismatch error” messages. I tried other variations but I get the impression that “Cut” and “Paste” and “Rows” and “Range” only accept constants to designate a range, not variables. What am I doing wrong? Thanks, Rob...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

can you make one worksheet update another
I have two similar worksheets within the same workbook. One sheet is sorted by name and the other sheet is sorted by a date for a speicfic name. Is there a way to change the date on the sheet, sorted by name, and that will automatically find the name on the other sheet(sorted by date) and change the date to the second sheet? Jaime, The normal way is to have one table, and sort it as needed when needed. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Jaime S." <Jaime S.@discussions.microsoft.com> wrote in mess...

how to find a second comma in a text
Hi, I have a text as --> Madison, Dane, Wisconsin How can I find the position of the second comma? thanks, Boon Assuming there is *always* a 2nd comma... =FIND(",",A2,FIND(",",A2)+1) -- Biff Microsoft Excel MVP "Boon" <boonyawat.la-ongthong@cnh.com> wrote in message news:OISZ4IGbKHA.2188@TK2MSFTNGP04.phx.gbl... > Hi, > > I have a text as --> Madison, Dane, Wisconsin > > How can I find the position of the second comma? > > thanks, > Boon > > thanks. this works well fo...

how to use TrackPopupMenu last parameter
Hi, I need to make a menu that does not disappear. (Odd, yes. I need to let users edit menu resources at runtime and would like to display the menus as how they would actually look.) So I'm looking at CMenu's TrackPopupMenu function, whose last parameter is LPCRECT lpRect. I figure if I set this rectangle to be (0, 0, screen width, screen height), then even if the user clicks elsewhere on the screen, the menu won't disappear, right? I tried the following code and the menu still disappears when I randomly click elsewhere in the application. What am I doing wrong? void CMenuVi...

how to find numbers that give another number
hi all. I need to make a macro which look up a column of an excel paper and find the numbers that once summed give the number in cell b5 for example!!they could be 2 or 3 ..or 4..and so on!! You would need the solver http://tinyurl.com/4doog example -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Rossella" <rossy77@gmail.com> wrote in message news:1145464775.044405.243700@v46g2000cwv.googlegroups.com... > hi all. > I need to make a macro which look up a column of an excel paper and > find the numbers that once summed give the number in ce...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

Open New Visio Drawing using VBA in Word?
Hi, We're creating new Word 2007 templates, and some of them require flowchart functionality from Visio. Our goal is to create a macro within the Word template to call Visio, open a new drawing and have a custom stencil set open for users to use. Users would then open create their flowchart/diagram in Visio, and it would update automatically into the Word template when Visio is closed. I'm using the following code to start Visio, however I'm stuck on where to go from there: Sub Visio() Shell "C:\Program Files\Microsoft Office\Office12\VISIO.exe", vbN...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Find and replace text thats not wdAlignParagraphCenter alignment
Im trying to make a macro that set alignment on all text to wdAlignParagraphJustify, except text that is wdAlignParagraphCenter. Right now i have this: With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphRight With .Replacement .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphJustify End With .Execute Replace:=wdReplaceAll End With With ActiveDocument.Content.Find .ClearFormatting .ParagraphFormat.Alignment = wdAlignParagraphLeft ...

Worksheets stutas meaning
Hi All, Does any body have a translation for the Worksheets stutas:- Compelet with error (Red worksheet) Compelet with warning (Pink Worksheet) some times the worksheet comeleted at store and the other gives error or warning, in case of transfers Worsheets, a store issue out the Qtys and the other store does not receive the transfer on the system, and gives in the HQ error or warning. Is there any other idea to aviod perform that transfer again? What those are mean, when error or warning happens, and why? I hope Microsoft add a decriptive error or wraning to let the user know what hap...

Help With Worksheet Operations
Is there any way in vba code to reproduce the same functionality in Excel as pressing Ctrl+right and/or Ctrl+down? I need a quick way to move to the last column and the last row on a spreadsheet. Also, is there a quick way to convert the worksheet to a string array or anything else (maybe a dataset)? Try Some Like this Sub MovetoEndDown() Range("A1").End(xlDown).Activate '(xlToRight) in case you want to move to the right. End Sub or Sub MoveDownPlus1Row() Dim NextRow As Variant NextRow = Range("A20000").End(xlUp).Row + 1 Cells(NextRow, 1).Activate End Sub Hope thi...

Find all the files in a folder
I had been using the FileSearch function to locate all the files of a particular type in a folder. Office2007 appears to have replaced that function with a FileSystemObject class, but i can't get that to work. Here is what I had been using. Any help would be appreciated. Set fs = Application.FileSearch With fs .LookIn = Range("C7").Value .SearchSubFolders = True .FileName = "*.csv" If .Execute() > 0 Then Row = 1 For i = 1 To .FoundFiles.Count Hi Phil This works in 2007 although it is not clear from your co...

Macro to find a row and delete it and the 9 rows above it. #2
Thanks alot that did the trick. This forum and you guys are life saver -- Rusty_n ----------------------------------------------------------------------- Rusty_nl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=671 View this thread: http://www.excelforum.com/showthread.php?threadid=31376 ...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...