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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=517527

try

activesheet.usedrange

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=517527

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" wrote in message
...
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"


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=517527

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" wrote in message
...
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