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

With Sheets("Data")
lrC = .Cells(.Rows.Count, 6).End(xlUp).Row
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

On Thursday, December 5, 2013 1:26:51 PM UTC-8, GS wrote:
With Sheets("Data")

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

End With



--

Garry



Okay, got it, thanks Garry.

On Thursday, December 5, 2013 3:35:20 PM UTC-8, Howard wrote:
On Thursday, December 5, 2013 1:26:51 PM UTC-8, GS wrote:

With Sheets("Data")



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



End With







--



Garry







Okay, got it, thanks Garry.

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.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Friday, December 6, 2013 12:39:54 AM UTC-8, Claus Busch wrote:
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.



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.



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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Friday, December 6, 2013 5:08:49 AM UTC-8, GS wrote:
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

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