Select Cells In Column that have data

[Guest]

How do I select cells in column A that have any type of "Data" in them, it
can be numbers, text, or combination. Just need a macro to autoselect
everything in column A that has data.

Thanks

Sean

 

[aidan.heritage]

You can record it - the actions are
data menu, filter, autofilter
filter on column a to Non Blanks

stop the recorder - macro done!

 

[paul.robinson]

Hi
Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers +
xlTextValues).Select

Will select cells with text or numbers. If you want formulas change to
xlTypeFormulas in specialcells. If you want constants and formulas, you
will have to do a set statement

Set ConstCells = Range("A:A").SpecialCells(xlCellTypeConstants,
xlNumbers + xlTextValues)

and same for FormulaCells. Then union the two ranges and select that.

regards
Paul

 

[Norman Jones]

--

 

[Norman Jones]

Hi Sean,

How do I select cells in column A that have any type of "Data" in them, it
can be numbers, text, or combination. Just need a macro to autoselect
everything in column A that has data.

Try:

'================>>
Public Sub PopulatedRange()
Dim WB As Workbook
Dim SH As Worksheet
Dim srcRng As Range
Dim destRng As Range
Dim RngA As Range, RngB As Range
Dim popRng As Range

Set WB = Workbooks("Book1.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set srcRng = SH.Range("A:A") '<<===== CHANGE

On Error Resume Next
Set RngA = srcRng.Cells. _
SpecialCells(xlCellTypeConstants)
Set RngB = srcRng.Cells. _
SpecialCells(xlCellTypeFormulas)

If Not RngA Is Nothing Then Set popRng = RngA

If Not RngB Is Nothing Then
If Not popRng Is Nothing Then
Set popRng = Union(RngB, popRng)
Else
Set popRng = RngB
End If
End If

popRng.Select

End Sub
'<<================