Hi
I have the following cod
Dim count1 As Intege
Worksheets("ABC").Activat
ActiveSheet.Range("AR:AR").Selec
count1 = Selection.Column.coun
For i = 1 To count
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(i)).Value
Nex

I want to know the last row number in column AR that contains the data (Column AR has blank cells too ) and run the loop equal number of times.
But every time I run this code, it gives an error on line "count1 = Selection.Column.count
Plz hel
Thnks

Hi
try something like the following:
Sub count_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = worksheets("ABC").Cells(Rows.Count, "AR").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "AR").Value < "" Then
ComboBox15.AddItem CStr(Cells(RowNdx, "AR").Value)
End If
Next RowNdx
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


Shilps wrote:
Hi,
I have the following code
Dim count1 As Integer
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
count1 = Selection.Column.count
For i = 1 To count1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(i)).Value)
Next

I want to know the last row number in column AR that contains the
data (Column AR has blank cells too ) and run the loop equal number
of times. But every time I run this code, it gives an error on line
"count1 = Selection.Column.count"
Plz help
Thnks

Hi Ships,

Even if you got the syntax correct, it would not work as you are counting
the columns in a range. As the range is just one column, this would always
be 1. You need to count the rows, like so

Worksheets("Sheet1").Activate
For i = 1 To ActiveSheet.Cells(Rows.Count, "AR").End(xlUp).Row
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(i)).Value)
Next

--

HTH

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

"Shilps" wrote in message
...
Hi,
I have the following code
Dim count1 As Integer
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
count1 = Selection.Column.count
For i = 1 To count1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(i)).Value)
Next

I want to know the last row number in column AR that contains the data
(Column AR has blank cells too ) and run the loop equal number of times.
But every time I run this code, it gives an error on line "count1 =
Selection.Column.count"
Plz help
Thnks

a slightly easier method...assumes the ActiveX combo...

Sub SetCombo()

With Worksheets("Sheet1")
.ComboBox15.ListFillRange = _
"E2:E" & .Range("E5000").End(xlUp).Row
End With

End Sub

This sets the combo to a table starting in row 2 with an
indeterminate number of rows

HTH
Patrick Molloy
Microsoft Excel MVP
-------------------
I Feel Great!

-----Original Message-----
Hi,
I have the following code
Dim count1 As Integer
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
count1 = Selection.Column.count
For i = 1 To count1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr
(i)).Value)
Next

I want to know the last row number in column AR that
contains the data (Column AR has blank cells too ) and
run the loop equal number of times.
But every time I run this code, it gives an error on
line "count1 = Selection.Column.count"
Plz help
Thnks
.

Hi Bob

As U said that I am trying to count the rows, I chaged the code to following
Dim c As Intege
Worksheets("ABC").Activat
ActiveSheet.Range("AR:AR").Selec
c1 = Selection.Rows.Coun
For c = 2 To c
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value
Next

But now it is giving error on lin
For c = 2 To c

But it is not giving error when I change the code t

Dim c As Intege
Worksheets("ABC").Activat
ActiveSheet.Range("AR:AR").Selec
c1 = MsgBox(Selection.Rows.Count, , no
For c = 2 To c
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value
Next

How does adding a msgbox statement change it
Also if I change the code to, it gives error on "ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value)

Dim c As Lon
Worksheets("ABC").Activat
ActiveSheet.Range("AR:AR").Selec
c1 = Selection.Rows.Coun
For c = 2 To c
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value
Next

TI
Shilps

Hi Shilps,

I don't know why it gives an error, but it is not good as it returns all of
the rows, that is 65536. You don't want this. What you want is

c1 = Cells(Rows.Count, "AR").End(xlUp).Row

try that and see if you still a combo error.
--

HTH

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

"Shilps" wrote in message
...
Hi Bob!

As U said that I am trying to count the rows, I chaged the code to
following :
Dim c As Integer
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
c1 = Selection.Rows.Count
For c = 2 To c1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value)
Next c

But now it is giving error on line
For c = 2 To c1

But it is not giving error when I change the code to

Dim c As Integer
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
c1 = MsgBox(Selection.Rows.Count, , no)
For c = 2 To c1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value)
Next c

How does adding a msgbox statement change it?
Also if I change the code to, it gives error on "ComboBox15.AddItem
CStr(Sheet1.Range("AR" + CStr(c)).Value)"

Dim c As Long
Worksheets("ABC").Activate
ActiveSheet.Range("AR:AR").Select
c1 = Selection.Rows.Count
For c = 2 To c1
ComboBox15.AddItem CStr(Sheet1.Range("AR" + CStr(c)).Value)
Next c

TIH
Shilps