Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange.Select - but is it?
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? -- donwb |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange.Select - but is it?
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 "donbowyer" wrote: 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? -- donwb |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange.Select - but is it?
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. -- Regards, Tom Ogilvy "donbowyer" wrote: 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? -- donwb |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UsedRange.Select - but is it?
Hi Joel & Tom
I'm using both your suggestions now. Many thanks Don -- donwb "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "donbowyer" wrote: 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? -- donwb |