[RESOLVED] Number of lines of a excel sheet

[gonzalex]

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.

[RobDog888]

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.

Sheet1.UsedRange.End(xlDown).row
Sheet1.UsedRange.End(xlToRight).Column
'Or
Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row
Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Column

[Ecniv]

msgbox "Last Row : " & sht.cells(65535,1).end(xlup).row

an alternative
- assumes column1 (a) has continous data
- sht is the worksheet - activesheet

[gonzalex]

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

[RobDog888]

For Each c In Worksheets("Sheeet1").Range("A1:A" & Range("A1").End(xlDown).Row).Cells

[gonzalex]

it gives the same error...

[RobDog888]

I see the other error now. Its a misspelled "Sheeet1".

For Each c In Worksheets("Sheet1").Range("A1:A" & Range("A1").End(xlDown).Row).Cells