Help with Selection Property
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 |
Help with Selection Property
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 |
Help with Selection Property
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 |
Help with Selection Property
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 . |
Help with Selection Property
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 |
Help with Selection Property
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 |
All times are GMT +1. The time now is 04:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com