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
>
>
>