Hello,
I have a excel sheet and i want to know the last line that is filled with data. I want to do this and next i want to loop every cells of column until the last line filled with data. Can anyone help me?
Thanks.
Printable View
Hello,
I have a excel sheet and i want to know the last line that is filled with data. I want to do this and next i want to loop every cells of column until the last line filled with data. Can anyone help me?
Thanks.
You can use either the .UsedRange or .SpecialCells to do this.
SpecialCells will give you the next available row/column where UsedRange will give the last "used" row/column.
VB Code:
Sheet1.UsedRange.End(xlDown).row Sheet1.UsedRange.End(xlToRight).Column 'Or Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Column
an alternativeCode:msgbox "Last Row : " & sht.cells(65535,1).end(xlup).row
- assumes column1 (a) has continous data
- sht is the worksheet - activesheet
Sub pinta()
Worksheets("Sheet1").Activate
For Each c In Worksheets("Sheeet1").Range("A1", Range("A1").End(xlDown)).Cells
Select Case c.Value
Case 9
'Worksheets("Sheet1").Range(c, Range(c).End(xlToRight)).Interior.Color = RGB(215, 235, 255)
c.Interior.Color = RGB(215, 235, 255)
End Select
Next
End Sub
i have tried this code but gives me a error. the error is this: "Run time error '9' : Subscript out of range"
can you help?
thanks
:)VB Code:
For Each c In Worksheets("Sheeet1").Range("A1:A" & Range("A1").End(xlDown).Row).Cells
it gives the same error...
I see the other error now. Its a misspelled "Sheeet1".
VB Code:
For Each c In Worksheets("Sheet1").Range("A1:A" & Range("A1").End(xlDown).Row).Cells