UsedRange.Select - but is it?

[Guest]

Excel 2003 on XP

I have a worksheet containing one contiguous block of cells containing data,
which I wish to select.
If I use ActiveSheet.UsedRange.Select it often selects a range greater
than the block of cells - ie includes cells apparently containing no data.
What am I doing wrong?

 

[Guest]

There are different reasons cells can appear empty an not be empty. Test the
cell with isemty to determine is it is really empty. You could have spaces
in the cell which will look empty. You could have formulas in the cell that
is returning nothing.

Use this code to remove the empty cells

Sub remove()
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Set colrange = Range(Columns(Lastcol + 1), Columns(Columns.Count))
Columns(colrange.Address).Delete
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Rows((lastrow + 1) & ":" & Rows.Count).Delete
End Sub

 

[Guest]

If the block started in cell A1, then

Range("A1").CurrentRegion.Select

would be what I would try.

If you region is bounded by at least one truly emtpy row and column on the
sides (0r the borders of the sheet), then that should work.

 

[Guest]

Hi Joel & Tom
I'm using both your suggestions now.
Many thanks
Don