PDA

View Full Version : Solved: Selecting a Rows with Data



willhh3
02-07-2011, 04:59 PM
This is probably an easy one.

When I delete a few rows of data, Excel remembers where that block of data was. So if I hit ctrl-end the cursor goes down to this last cell which may contain blank rows. This corrects itself when I save and reopen the workbook. So in the attached example, delete a couple rows, put the cursor in A1 and then hit ctrl-end and you’ll see what I mean. I want to run a formatting macro and not have to close and reopen. So what I’d like to do is have a bit of code that will select down to the last row of actual data and over to the last column that has any data (column L in the sample). Basically I want it to ignore any rows that are completely blank and know it needs to go over to L. If it helps column L will always be the last column, however the number of rows with data can vary.

Below is the code I’ve been playing with. It does what I want, but it selects the blank rows - which then in turn get formatted when I run my macro. I hope this makes sense and thanks in advance for any help provided.

' Range("A1").Select
'Range(Selection, Selection.SpecialCells(xlLastCell)).Select


Whh3

Kenneth Hobs
02-07-2011, 07:43 PM
I don't see that behavior. You will see that using SpecialCells though which is known to work that way.

See if this helps. It goes from the bottom up. You can use End(xlDown) to get the first row to the last row before a blank cell in column A. Column A usually governs these things though other methods can be used.
Sub Test()
Dim r As Range
Set r = Worksheets("Sheet1").UsedRange
MsgBox r.Address, , "Includes Formatted Cells"

Set r = Worksheets("Sheet1").Range("A1", _
Worksheets("Sheet1").Range("L" & _
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row))
MsgBox r.Address, , "Includes Only Used Range With Data"
End Sub

willhh3
02-08-2011, 05:05 PM
Odd you couldn't replicate the behavior. I get it consistently. Any way your code worked and got me on track for the rest of my task.

Thanks!
Whh3