Row index in a column that has no data below 1

[DonyBoy]

is there a way to find the last cell in, say, Column B. The thing is, column B has some empty cells here and there. I want to be able to extract the row-index (eg, 17th row) that has no data whatsoever below it

 

[PHV]

Have a look in the FAQ area of this forum.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DonyBoy]

thanks.

i tried using this code that I found in the FAQ section

Sub FindLastCell()
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = ActiveSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(Rows.Count, i), Cells(Rows.Count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i
lastcell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
'MsgBox lastcell.Rows
End Sub


How do I then extract the row number only from the lastcell address?

 

[DonyBoy]

sorry, i was being stupid... it's just mRow isn't it..sorry again

 

[DonyBoy]

actually the above method does give incorrect answers soemtimes. I'm not blamming your code.. most probably i'm doing somethign stupid...soem help will be appreciated...

 

[SkipVought]

\
Hi,

"...incorrect answers soemtimes"

Under what conditions?

Skip,
[sub]
[red][/red]
[/sub]

 

[DonyBoy]

I’m reading the last row in a sheet called “GANT”, but this function is actually called within worksheet_change event of a DIFFERENT sheet- Sheet2.

The cose is counting the last row in Sheet2 and not GANT. Please see the code below.

-----------------
Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("GANT").Select

'///Find the last row with any data and call it mRow

Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = ActiveSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = Range(Cells(Rows.Count, i), Cells(Rows.Count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
Else
End If
Next i
lastcell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address

maxrow = mRow


If Target.Row < 51 And Target.Column < 3 Then

Dim oCell As Range

t = 1
For Each oCell In Target


namechanged = Sheets("Colour Coding").Cells(Target.Row, 1)

For p = 1 To mRow
.....
.....

Next oCell

End If

End Sub

 

[PHV]

A starting point:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oSheet As Worksheet
Set oSheet = Sheets("GANT")
'///Find the last row with any data and call it mRow
Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
lCol = oSheet.UsedRange.Columns.Count
mRow = 0
For i = 1 To lCol
lRow = oSheet.Range(oSheet.Cells(oSheet.Rows.Count, i), oSheet.Cells(oSheet.Rows.Count, i)).End(xlUp).Row
If lRow > mRow Then
mRow = lRow
mCol = i
End If
Next i
lastcell = oSheet.Range(oSheet.Cells(mRow, mCol), oSheet.Cells(mRow, mCol)).Address
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DonyBoy]

Thanks very much...that was most helpful..