Looking for a way to Set Range to Entire Column

[Dan Thompson]

Here is my code

Sub Temp()
Dim R
'Set ColA = ActiveSheet.Range("A1:A65536")
Set ColA = ActiveSheet.Columns(1).EntireColumn


For Each R In ColA
If R.Text = "" Then
endrow = R.Row - 1
Exit For
End If
Next
MsgBox endrow
End Sub

The first line I have rem'ed out was what I originally was using and it does
work for this code however I was hoping that I could do the same thing by
using just a column reference to set my range for the rest of my code but
when I use the second line after the rem'ed out line it doesn't return the
same as my original line of code. Can I use somthing like the second line of
my code or do I have to use the first one to set my range ?

Dan Thompson

 

[Gary''s Student]

Set ColA = ActiveSheet.Range("A:A")

 

[Jacob Skaria]

Dim ColA As Range
Set ColA = ActiveSheet.Columns(1)

 

[Patrick Molloy]

for the last row in a column

Function LastRow(col As Long) As Long
Dim cell As Range
Set cell = Columns(col).SpecialCells(xlCellTypeLastCell)
LastRow = cell.Row
Set cell = Nothing
End Function

 

[Jim Cone]

ColA is returning a column not the cells in the column.
Try... Msgbox ColA.Count
and then make two changes...

Dim R as Range
Set ColA = ActiveSheet.Columns(1).Cells

Now again try... Msgbox ColA.Count
--
Jim Cone
Portland, Oregon USA



"Dan Thompson"
<[email protected]>
wrote in message
Here is my code

Sub Temp()
Dim R
'Set ColA = ActiveSheet.Range("A1:A65536")
Set ColA = ActiveSheet.Columns(1).EntireColumn


For Each R In ColA
If R.Text = "" Then
endrow = R.Row - 1
Exit For
End If
Next
MsgBox endrow
End Sub

The first line I have rem'ed out was what I originally was using and it does
work for this code however I was hoping that I could do the same thing by
using just a column reference to set my range for the rest of my code but
when I use the second line after the rem'ed out line it doesn't return the
same as my original line of code. Can I use somthing like the second line of
my code or do I have to use the first one to set my range ?

Dan Thompson

 

[Rick Rothstein]

ActiveSheet.Columns(1) is still correct. Because Columns returns a column
(of cells), there is only one column in it (what Count returns); however,
that column is still made up of 65536 cells (for versions of Excel prior to
2007) which can be seen by simply referencing them...

This returns 1 as an answer...

MsgBox.ActiveSheet.Columns(1).Count

but this returns 65536 as an answer...

MsgBox ActiveSheet.Columns(1).Cells.Count

 

[Jim Cone]

Rick,
Re: ActiveSheet.Columns(1)
Try looping thru the cells in the column as the original poster wanted to do.
'--
Jim Cone (D582)




"Rick Rothstein"
wrote in message
ActiveSheet.Columns(1) is still correct. Because Columns returns a column
(of cells), there is only one column in it (what Count returns); however,
that column is still made up of 65536 cells (for versions of Excel prior to
2007) which can be seen by simply referencing them...

This returns 1 as an answer...
MsgBox.ActiveSheet.Columns(1).Count

but this returns 65536 as an answer...
MsgBox ActiveSheet.Columns(1).Cells.Count

 

[Dan Thompson]

Thanks Jim your solution worked

 

[Rick Rothstein]

The difference between what you originally posted and what I was attempting
to convey is minimal to be sure. I was just pointing out you could set ColA
to Columns(1) and then just reference its Cells property when needed. You
referenced the Cells property in the Set statement whereas I was (trying) to
indicate it could be referenced when needed. For example, here is the OP's
original loop modified to do this (notice where I referenced the Cells
property)...

Sub Temp()
Dim R As Range
Dim ColA As Range
Dim EndRow As Long
Set ColA = ActiveSheet.Columns(1)
For Each R In ColA.Cells
If R.Text = "" Then
EndRow = R.Row - 1
Exit For
End If
Next
MsgBox EndRow
End Sub

 

[Jefgorbach]

Here is my code

Sub Temp()
Dim R
'Set ColA = ActiveSheet.Range("A1:A65536")
Set ColA = ActiveSheet.Columns(1).EntireColumn

For Each R In ColA
    If R.Text = "" Then
        endrow = R.Row - 1
        Exit For
    End If
Next
MsgBox endrow
End Sub

The first line I have rem'ed out was what I originally was using and it does
work for this code however I was hoping that I could do the same thing by
using just a column reference to set my range for the rest of my code but
when I use the second line after the rem'ed out line it doesn't return the
same as my original line of code. Can I use somthing like the second lineof
my code or do I have to use the first one to set my range ?

Dan Thompson

Perhaps I'm overlooking something, but off the top of my head it
appears you're looping thru the entire column from top to bottom
seeking the last used row -- in which case you should be able to save
time/coding by replacing the entire loop with a single line:

sub temp()
endrow = Range("A65536").End(xlUp).End).Row
msgbox endrow
end sub