Select only Range of Cells with Data with Macro

  • Thread starter Thread starter Steve__
  • Start date Start date
S

Steve__

I want in a macro to be able to select only the range of
cells that contain data. What is the macro formula to
select this data?

Note: The beginning range will always be A1, however the
end range (ctrl+end) will vary.

I know I can select the end of the range via a macro
with:

ActiveCell.SpecialCells(xlLastCell).Select

However, that is where I am stuck.

Thanks!
 
One way:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select

Or, if you want both constants and formulas:

With ActiveSheet.Cells
Union(.SpecialCells(xlCellTypeConstants), _
.SpecialCells(xlCellTypeFormulas)).Select
End With

Note: If you may not have either formulas or constants, wrap the
above with

On Error Resume Next
'selection statement(s)
On Error GoTo 0
 
Thanks.

This one seems to work fine:

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select

However, I don't have formula's in my test example, so I
tried the next example you had. When I try this one, I
get a "Error 1004" " No cells were found" error.

With ActiveSheet.Cells
Union(.SpecialCells(xlCellTypeConstants), _
.SpecialCells(xlCellTypeFormulas)).Select
End With

Any suggestions?
 
Thanks J.E.

I tried the OnError with no luck. I thought I had it, but
I must have it formatted incorrectly. I see the concept
you have, but must have something in the wrong order. If
you don't mind, can you paste the exact text, including
the OnError text, exactly as I should paste it myself?

Thanks for all your help and suggestions.
Steve
 
Thanks J.E.

I got it now. Disregard the other question in this
thread. Your answer in my other thread solves it for me.
Thanks again! :-)

Steve
 
I may have misunderstood. The following will select constants, if
there are any, and formulas, if there are any.

On Error Resume Next
With ActiveSheet.Cells
.SpecialCells(xlCellTypeConstants).Select
If Err = 0 Then
Union(Selection, _
.SpecialCells(xlCellTypeFormulas)).Select
Else
.SpecialCells(xlCellTypeFormulas).Select
End If
End With
On Error GoTo 0
 
Back
Top