Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

Here's the code that I got off of someone else for find the last row in
a given column.

LastRow = Columns("A").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Die_Another_Day
bodhisatvaofboogie wrote:
Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

I'm curious how to plug a last row formula into my code.

so

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
+
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
= WHAT???

I've used that lastrow formula for a lot of other things and it has worked
pretty well....but I don't know how to combine the two. Any suggestions?
THANKS!!!

"bodhisatvaofboogie" wrote:

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

I don't quite understand what you are doing. can you post more of your
code?

Die_Another_Day

bodhisatvaofboogie wrote:
I'm curious how to plug a last row formula into my code.

so

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
+
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
= WHAT???

I've used that lastrow formula for a lot of other things and it has worked
pretty well....but I don't know how to combine the two. Any suggestions?
THANKS!!!

"bodhisatvaofboogie" wrote:

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

On Error Resume Next
Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
On Error GoTo 0

This is my current code....It selects all blanks in column A, offsets them
up one row (selecting the cell above it) then adds the color highlighting of
Yellow (.colorindex = 6) to each cell selected.


My problem with it is that it will select ALL blank cells in column A right
on down to row 65000....well my data set is not that long, and I end up with
a bunch of highlghted cells from the bottom of the data set to row 65000.
Normally that wouldn't be so bad, but that screws up the rest of the macro.
Essentially I need to have it only select the blanks within the range of my
data set..IE: go to the last row and stop highlighting. Make sense???

"Die_Another_Day" wrote:

I don't quite understand what you are doing. can you post more of your
code?

Die_Another_Day

bodhisatvaofboogie wrote:
I'm curious how to plug a last row formula into my code.

so

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
+
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
= WHAT???

I've used that lastrow formula for a lot of other things and it has worked
pretty well....but I don't know how to combine the two. Any suggestions?
THANKS!!!

"bodhisatvaofboogie" wrote:

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

Try Range("A1",Range("A" & LastRow)).SpecialCells......Yada yada yada
instead of all of column a
HTH

Die_Another_Day
bodhisatvaofboogie wrote:
On Error Resume Next
Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
On Error GoTo 0

This is my current code....It selects all blanks in column A, offsets them
up one row (selecting the cell above it) then adds the color highlighting of
Yellow (.colorindex = 6) to each cell selected.


My problem with it is that it will select ALL blank cells in column A right
on down to row 65000....well my data set is not that long, and I end up with
a bunch of highlghted cells from the bottom of the data set to row 65000.
Normally that wouldn't be so bad, but that screws up the rest of the macro.
Essentially I need to have it only select the blanks within the range of my
data set..IE: go to the last row and stop highlighting. Make sense???

"Die_Another_Day" wrote:

I don't quite understand what you are doing. can you post more of your
code?

Die_Another_Day

bodhisatvaofboogie wrote:
I'm curious how to plug a last row formula into my code.

so

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
+
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
= WHAT???

I've used that lastrow formula for a lot of other things and it has worked
pretty well....but I don't know how to combine the two. Any suggestions?
THANKS!!!

"bodhisatvaofboogie" wrote:

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select


This formula finds all the blank spaces then moves up one and selects the
cells. The problem is that it selects all the way to the bottom of Excel,
line 65000 SO, how do I get it to just select to the last row in the
dataset??? THANKS!!!

Range(("A1"), Range("A65500").End(xlUp)).Select

Just in the interest with compatibility with Excel 2007 which as
many more rows than 65500, you should use

Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"excelent" wrote in message
...
Range(("A1"), Range("A65500").End(xlUp)).Select