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

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

--
Dennis

try

activesheet.usedrange

regards

try

activesheet.usedrange

regards

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)

"Dennis" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> Dennis

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"

Thank you all for your help
--
Dennis


"Bob Phillips" wrote:

>
> 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)
>
> "Dennis" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > Dennis
>
>
>