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)

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

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)

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

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

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

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

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)

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

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

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

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?
"Bob Phillips" wrote in message
...
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)

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

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

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

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)

"Rich Cooper" wrote in message
...
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?
"Bob Phillips" wrote in message
...
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)

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

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

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