Return last row no. of column with blanks at top

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I have column F that the data starts in F5 to F?
I want the last row number.
All these examples return F5, one errors out.

I can't believe I don't have an example in cheat sheet.

Thanks.
Howard

lrC = Sheets("Data").Cells(Rows.Count, 6).End(xlUp).Row

lrC = Range("F5:F" & Range("F5").End(xlDown)).Row

If WorksheetFunction.CountA(Cells) > 0 Then
lrC = Cells.Find(What:="*", After:=[F5], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If

MsgBox lrC
 
Okay, got it, thanks Garry.
Click to expand...

Well, I thought I had it.
I'm trying to set up an example to refer to that will define a mid column variable range and select it, one range on the sheet that has the code in its module, and one range on an adjacent sheet.

The commented out lines for rangeF work fine for the sheet with the code in its module. No need to mess with them.

The With Sheets("Data") lines for the rangeH error out after the Msgbox line. The msgbox displays the correct value for the last row on the Data sheet.
Any combination of the lines I have commented out or are using produces an error.

Thanks.
Howard

Option Explicit

Sub xx()
Dim rangeF As Range
Dim rangeH As Range
Dim lr As Long
Dim lrH As Long

'lr = Range("F" & Rows.Count).End(xlUp).Row
'MsgBox lr
'Set rangeF = Range("F6:F" & lr)
'rangeF.Select

With Sheets("Data")
lrH = .Cells(.Rows.Count, 8).End(xlUp).Row
MsgBox lrH
Set rangeH = Range("H6:H" & lrH)
'Set rangeH = Sheets("Data").Range("H6:H" & lrH)
rangeH.Select
'Sheets("Data").rangeH.Select
End With

End Sub
 
Hi Howard,

Am Thu, 5 Dec 2013 22:23:38 -0800 (PST) schrieb Howard:
The commented out lines for rangeF work fine for the sheet with the code in its module. No need to mess with them.

The With Sheets("Data") lines for the rangeH error out after the Msgbox line. The msgbox displays the correct value for the last row on the Data sheet.
Any combination of the lines I have commented out or are using produces an error.
Click to expand...

if you don't refer to a sheet the code takes the active sheet. To run
the code from each sheet use 2 With statements.
The code must be in a Standard Module
You can't select a range if the sheet is not active. But activate the
sheet and then select the range is not good. Better use Application.Goto

Sub xx()
Dim rangeF As Range
Dim rangeH As Range
Dim lr As Long
Dim lrH As Long

With Sheets("Sheet1")
lr = .Cells(.Rows.Count, "F").End(xlUp).Row
MsgBox lr
Set rangeF = .Range("F6:F" & lr)
Application.Goto rangeF
End With

With Sheets("Data")
lrH = .Cells(.Rows.Count, "H").End(xlUp).Row
MsgBox lrH
Set rangeH = .Range("H6:H" & lrH)
Application.Goto rangeH
End With

End Sub


Regards
Claus B.
 
Hi Howard,



Am Thu, 5 Dec 2013 22:23:38 -0800 (PST) schrieb Howard:







if you don't refer to a sheet the code takes the active sheet. To run

the code from each sheet use 2 With statements.

The code must be in a Standard Module

You can't select a range if the sheet is not active. But activate the

sheet and then select the range is not good. Better use Application.Goto



Sub xx()

Dim rangeF As Range

Dim rangeH As Range

Dim lr As Long

Dim lrH As Long



With Sheets("Sheet1")

lr = .Cells(.Rows.Count, "F").End(xlUp).Row

MsgBox lr

Set rangeF = .Range("F6:F" & lr)

Application.Goto rangeF

End With



With Sheets("Data")

lrH = .Cells(.Rows.Count, "H").End(xlUp).Row

MsgBox lrH

Set rangeH = .Range("H6:H" & lrH)

Application.Goto rangeH

End With



End Sub





Regards

Claus B.
Click to expand...



Thanks Claus, and the small explanation is excellent.
I have copied it also to go with the code in my cheat sheet.

Thanks again.

Howard
 
I was going to reply similar to Claus for the With...End With block.

With Sheets("Data")
lrH = .Cells(.Rows.Count, "H").End(xlUp).Row
MsgBox lrH: Application.Goto .Range("H6:H" & lrH)
End With

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I was going to reply similar to Claus for the With...End With block.



With Sheets("Data")

lrH = .Cells(.Rows.Count, "H").End(xlUp).Row

MsgBox lrH: Application.Goto .Range("H6:H" & lrH)

End With



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com
Click to expand...

Thanks, Garry.

I have the example and the snippet of info tucked away on my cheat sheet.
I have a hunch "Application.Goto .Range("H6:H" & lrH)" will be a pretty good tool.

Howard
 
Back
Top