find the FIRST USED row/column?

crmpicco · December 22nd, 2005, 05:36 AM

Code:

Last_Col = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
Last_Row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

i am using this code to find the LAST USED row/column,
is there code to find the FIRST USED row/column?

thanks.

Picco

www.crmpicco.co.uk

maxpotters · December 25th, 2005, 03:43 AM

Hi,

I never used this code, but this doesn't seem like a hard problem, so i tested this:

Last_Col = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlNext).Column
Last_Row = Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlNext).Row

Just change xlPrevious into xlNext, now it will find the first cell used.

-Max

Shasur · December 30th, 2005, 02:21 AM

Hi

You can use the specialcells method to get the last row

lLastRow = ActiveSheet.cells.specialcells(xlcelltypelastcell).row

Sadly, there is no xlcelltypefirstcell in Excel:(

However, use the usedrange count to get the range

lUsedRow = ActiveSheet.usedrange.rows.count

If lLastRow = LUsedRow then
lFirstRow = 1
Else
lFirstRow = lLastRow - LUsedRow + 1
End if

Shasur

Jeremy7 · July 23rd, 2013, 12:52 PM

This bit of code finds the first cell as long as it is within the first 500 cells. It is written to look in column A, but can look in whichever column of course.

Code:

Sub findfirst()

Dim x As Integer
x = 0

For i = 1 To 500

    If Not Cells(i, 1) = "" Then
    MsgBox "First Cell is: " & i
    x = 1
    End If
    
    If x = 1 Then
    GoTo lastline
    End If
    
Next i
    
lastline:

End Sub