Looking for a way to Set Range to Entire Column
 

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

Looking for a way to Set Range to Entire Column
 

Set ColA = ActiveSheet.Range("A:A")
--
Gary''s Student - gsnu200854

Looking for a way to Set Range to Entire Column
 

Dim ColA As Range
Set ColA = ActiveSheet.Columns(1)
--
If this post helps click Yes
---------------
Jacob Skaria


"Dan Thompson" wrote:

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

Looking for a way to Set Range to Entire Column
 

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



"Dan Thompson" 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

Looking for a way to Set Range to Entire Column
 

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"

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

Looking for a way to Set Range to Entire Column
 

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

--
Rick (MVP - Excel)


"Jim Cone" wrote in message
...

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"

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

Looking for a way to Set Range to Entire Column
 

Rick,
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
--
Rick (MVP - Excel)

Looking for a way to Set Range to Entire Column
 

Thanks Jim your solution worked

"Jim Cone" wrote:


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"

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

Looking for a way to Set Range to Entire Column
 

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

--
Rick (MVP - Excel)


"Jim Cone" wrote in message
...
Rick,
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
--
Rick (MVP - Excel)

Looking for a way to Set Range to Entire Column
 

On May 22, 1:02*pm, Dan Thompson
wrote:
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

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