[RESOLVED] Number of lines of a excel sheet

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

Code:

---

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

---

an alternative
- 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