MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Loop If statment. Can this work? Please check.


Posted by Jason on November 19, 2001 2:54 PM

I'm trying to get this sub to find the column number of the last column in a sheet that still has data. Is there a way to make this work? Am I just typing it wrong? Or does anyone know a better way to do this? Thanks

Count = 1
Do Until True
If Cells(1, 1 + Count) = Empty Then
C = Count - 1
Else: Count = Count + 1
End If
Loop


Posted by faster on November 19, 2001 3:24 PM

SpecialCells(xlCellTypeLastCell).Column

this is another way to find the last cell (column)

Sub LastColumn()
'you may need to save to find the actual last cell
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
End Sub

Posted by Aladin Akyurek on November 19, 2001 3:25 PM

I'd suppose no ":" after Else for one thing?

Aladin

Posted by Jason on November 19, 2001 3:32 PM

Works Great. Thanks

Posted by Juan Pablo on November 21, 2001 9:31 AM

Aladin, i don't think this ":" would create an error. When you put ":" is just like an enter, without the enter, for example, this

For i = 1 to 10
MsgBox i
Next i

is equivalent to

For i = 1 to 10:MsgBox i:Next i

But this is just one line...(Altough is isn't in reality !)

Juan Pablo