Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
is there a way to find last column that has data in a given worksheet??
without selecting the range of data. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
You can use this function...
Function MaxColInUse(Optional WS As Worksheet) As Long Dim X As Long Dim LastCol As Long Dim Rw As Variant If WS Is Nothing Then Set WS = ActiveSheet With WS For Each Rw In .UsedRange.Rows On Error Resume Next LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column For X = LastCol To 0 Step -1 If .Cells(Rw.Row, X).Value < "" Then LastCol = X Exit For End If Next If LastCol MaxColInUse Then MaxColInUse = LastCol Next End With End Function Rick "guest" wrote in message ... is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Here is a function that returns the last column...
sub test msgbox lastcolumn 'activesheet msgbox lastcolumn(sheets("Sheet1")) end sub Public Function LastColumn(Optional ByVal wks As Worksheet) As Long Dim lngLastColumn As Long If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next LastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If LastColumn = 0 Then lngLastColumn = 1 End Function -- HTH... Jim Thomlinson "guest" wrote: is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Can't you just use
ActiveCell.SpecialCells(xlLastCell).Select On Jun 13, 2:43 pm, guest wrote: is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Not reliably. Try this... put some data in some of the rows out to Column 5
on a sheet and then put something in J3. Go to the Immediate window and enter this... ? ActiveCell.SpecialCells(xlLastCell).Column It should print out 10. Now issue this command in the Immediate window Range("J3").Delete That will remove the entry from J3 that you just put in it. Look at the worksheet... the last used column is now 5. Go back and execute this line again... ? ActiveCell.SpecialCells(xlLastCell).Column It should still print out 10. Rick "Reitanos" wrote in message ... Can't you just use ActiveCell.SpecialCells(xlLastCell).Select On Jun 13, 2:43 pm, guest wrote: is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Guest...
On an empty sheet this function returns zero. While that is correct you can not reference column 0 which can lead to a run time error. Just something to be aware of... The function I posted returns 1 on an empty sheet. That may or may not be correct depending on what you want to do. -- HTH... Jim Thomlinson "Rick Rothstein (MVP - VB)" wrote: You can use this function... Function MaxColInUse(Optional WS As Worksheet) As Long Dim X As Long Dim LastCol As Long Dim Rw As Variant If WS Is Nothing Then Set WS = ActiveSheet With WS For Each Rw In .UsedRange.Rows On Error Resume Next LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column For X = LastCol To 0 Step -1 If .Cells(Rw.Row, X).Value < "" Then LastCol = X Exit For End If Next If LastCol MaxColInUse Then MaxColInUse = LastCol Next End With End Function Rick "guest" wrote in message ... is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Thanks Jim and Rick for your help.
"Rick Rothstein (MVP - VB)" wrote: Not reliably. Try this... put some data in some of the rows out to Column 5 on a sheet and then put something in J3. Go to the Immediate window and enter this... ? ActiveCell.SpecialCells(xlLastCell).Column It should print out 10. Now issue this command in the Immediate window Range("J3").Delete That will remove the entry from J3 that you just put in it. Look at the worksheet... the last used column is now 5. Go back and execute this line again... ? ActiveCell.SpecialCells(xlLastCell).Column It should still print out 10. Rick "Reitanos" wrote in message ... Can't you just use ActiveCell.SpecialCells(xlLastCell).Select On Jun 13, 2:43 pm, guest wrote: is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
I guess that situation needed to be handled (that is, if you could not be
sure it would be executed against an empty worksheet), the calling routine could do something like this... LastCol = MaxColInUse If LastCol 0 Then ' ' Do whatever... ' End If which assumes LastCol would be put into use within the If-Then block itself. If that was not the case for some reason, then this could be shortened to this... If MaxColInUse 0 Then ' ' Do whatever... ' End If although I can't think of any situation off the top of my head where the latter would be the case. Rick "Jim Thomlinson" wrote in message ... Guest... On an empty sheet this function returns zero. While that is correct you can not reference column 0 which can lead to a run time error. Just something to be aware of... The function I posted returns 1 on an empty sheet. That may or may not be correct depending on what you want to do. -- HTH... Jim Thomlinson "Rick Rothstein (MVP - VB)" wrote: You can use this function... Function MaxColInUse(Optional WS As Worksheet) As Long Dim X As Long Dim LastCol As Long Dim Rw As Variant If WS Is Nothing Then Set WS = ActiveSheet With WS For Each Rw In .UsedRange.Rows On Error Resume Next LastCol = .Cells(Rw.Row, Columns.Count).End(xlToLeft).Column For X = LastCol To 0 Step -1 If .Cells(Rw.Row, X).Value < "" Then LastCol = X Exit For End If Next If LastCol MaxColInUse Then MaxColInUse = LastCol Next End With End Function Rick "guest" wrote in message ... is there a way to find last column that has data in a given worksheet?? without selecting the range of data. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
On Jun 13, 10:43*pm, guest wrote:
is there a way to find last column that has data in a given worksheet?? without selecting the range of data. ActiveWorksheet.UsedRange.Rows.Count |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Of course!
ActiveWorksheet.UsedRange.Rows.Count UsedRange.Columns.Count |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
I know that saving the workbook will reset that immediately.
I could have sworn there was a command to do it, but my addled brain can't seem to remember :( On Jun 13, 4:24 pm, "Rick Rothstein \(MVP - VB\)" wrote: Not reliably. Try this... put some data in some of the rows out toColumn5 on a sheet and then put something in J3. Go to the Immediate window and enter this... ? ActiveCell.SpecialCells(xlLastCell).Column It should print out 10. Now issue this command in the Immediate window Range("J3").Delete That will remove the entry from J3 that you just put in it. Look at the worksheet... thelastusedcolumnis now 5. Go back and execute this line again... ? ActiveCell.SpecialCells(xlLastCell).Column It should still print out 10. Rick "Reitanos" wrote in message ... Can't you just use ActiveCell.SpecialCells(xlLastCell).Select On Jun 13, 2:43 pm, guest wrote: is there a way to findlastcolumnthat has data in a given worksheet?? without selecting the range of data. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Try this... insert a new sheet into your workbook, go to it, and put any
values you like in C3, D4 and E5 only (in other words, on a brand new sheet, do not put anything into at least the first column)... then execute your statement and see what you get. Rick "NOPIK" wrote in message ... Of course! ActiveWorksheet.UsedRange.Rows.Count UsedRange.Columns.Count |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
do not put anything into at least the first column)... then execute your
statement and see what you get. Thanks! I will fix my macros for this! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
column count
Ah, I found it:
Activesheet.Usedrange ActiveCell.SpecialCells(xlLastCell).Select But I guess it would be easier to just use: Activesheet.Usedrange.Column On Jun 13, 2:43 pm, guest wrote: is there a way to findlastcolumnthat has data in a given worksheet?? without selecting the range of data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to count#cells w/= value in other column and not count blank c | Excel Worksheet Functions | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. | Excel Worksheet Functions |