How to define a range based on first and last cells with data?

[Dennis]

This is my first Excel macro. I can get this code to work on a defined range
like "A1:A10". But how can I code the macro to select the range by first and
last cells with data? This is my code so far...

Sub Macro1()

'1. Column reference will always be constant, rows will be variable
'2. Find first non-zero length cell
'3. Find the last cell with data
'4. Select range
'5. Make each value in selected range a working hyperlink


Dim FirstRow As Long
Dim LastRow As Long
FirstRow = Worksheets("Sheet1").Range("a1").End(xlDown).Row + 1 ' this
will give you the row of the 1st blank cell in A
LastRow = Worksheets("Sheet1").Range("a65536").End(xlUp).Row + 1 ' this
will give you the row of the last blank cell in A


Dim myRange As Range
Dim myVar As Range

'This is where I ran into trouble...
Set myRange = Range("A" & "'" & blankrow & "'" & ":A" & "'" & myvalue & "'"
& ") 'set range

myRange.Select


'Microsoft KB271856
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub

 

[tony h]

try

activesheet.usedrange

regards

 

[tony h]

try

activesheet.usedrange

regards

 

[Bob Phillips]

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstRow = Cells(1, "A").End(xlDown).Row
Set myRange = Range(Cells(FirstRow, "A"), Cells(LastRow, "A"))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[mudraker]

Dennis

You can try Tony's suggestion.

or you can try this which fixes the error in your code.

Please note that you do not need to select a workbook, worksheet, range
etc to work with them - some example listed below your macro

Sub Macro1()

'1. Column reference will always be constant, rows will be variable
'2. Find first non-zero length cell
'3. Find the last cell with data
'4. Select range
'5. Make each value in selected range a working hyperlink


Dim FirstRow As Long
Dim LastRow As Long
FirstRow = Worksheets("Sheet1").Range("a1").End(xlDown).Row + 1 ' this
will give you the row of the 1st blank cell in A
LastRow = Worksheets("Sheet1").Range("a65536").End(xlUp).Row + 1 '
this
will give you the row of the last blank cell in A


Dim myRange As Range
Dim myVar As Range

'This is where I ran into trouble...
Set myRange = Range("A" & FirstRow & ":A" & LastRow) 'set range


'Microsoft KB271856
For Each xCell In myRange
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell

End Sub


range("a1").value = "Hi"
workbooks("Book1").sheets(1).cells(1,1).value = "Hello"

 

[Dennis]

Thank you all for your help
--
Dennis

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstRow = Cells(1, "A").End(xlDown).Row
Set myRange = Range(Cells(FirstRow, "A"), Cells(LastRow, "A"))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)