Creating Ranges

  • Thread starter Thread starter Rich Cooper
  • Start date Start date
R

Rich Cooper

I am trying to create a range. What i am doing is i have data that in
colums, A doctors last name in column a and first in column b. I have
figure out how to get the data like lastname, firstname. I have code that
tells me the last row and first row. For example it tells me that the first
row where there is an a as the first letter is 3, and the last row is 482.
I want to make rows 3-482 a range, but i can't figrue out how. Here is my
code:

Any help would be great.

Sub mg()
Dim Ridex As Long
Dim rng As Range, cell As Range
Dim LastRow As Integer, frow As Integer


Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
rng.Offset(0, 35).Formula = "=trim(H1) & "", "" & trim(I1)"
rng.Offset(0, 35).Formula = rng.Offset(0, 35).Value
LastRow = Worksheets("demog").Cells(Rows.count, "AJ").End(xlUp).Row

For Ridex = 1 To LastRow
If UCase(Left(Cells(Ridex, "AJ").Value, 1)) = "A" Then
frow = Ridex
Exit For
End If
Next



End Sub
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Set rng = Rows(CStr(frow) & ":" & CStr( LastRow))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
this is what i did. Still not working. Can you help me anymore?

Sub mg()
Dim Ridex As Long
Dim rng As Range, cell As Range
Dim LastRow As Integer, frow As Integer
Dim nn As Range

Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
rng.Offset(0, 35).Formula = "=trim(H1) & "", "" & trim(I1)"
rng.Offset(0, 35).Formula = rng.Offset(0, 35).Value
LastRow = Worksheets("demog").Cells(Rows.count, "AJ").End(xlUp).Row

For Ridex = 1 To LastRow
If UCase(Left(Cells(Ridex, "AJ").Value, 1)) = "A" Then
frow = Ridex
Exit For
End If
Next

Set nn = Rows(CStr(frow) & ":" & CStr(LastRow))



End Sub
 
In what way is it not working? My limited test worked fine.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I am trying to use that range it suppodesly creates in a list box. I am
putting in nn as the listbox range reference and it doesn't work. if i
manually define the range it works. Even when i run the code it doesn't
show the range in the defined name. Is there a way i can check to see if
the range is being created?
 
Ah well, you asked for a range of those rows, and that is what you got. This
will be no good in a listbox as you need part of those rows, say column A,
something like

Set nn = Range("A" & frow & ":A" & LastRow)

You get what you ask for, so ask for what you want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top