VBA to select all used cells in row A only?

[Human_doing]

Hi,

Can anyone please provide the VBA to select all used cells in row A only? I have seen some great VBA on here to select all used cells on a worksheet etc. but am looking to select only row a.

Thanks

 

[Peter_SSs]

A few things ...

1. Rows are usually referred to by number, columns by letter. So do you mean Row 1 or Column A?

2. Selecting things in vba is rarely necessary and generally slows your code. So what are you going to do after selecting these cells? Pehaps the outcome can be achieved more efficiently without the need to select them at all.

3. Does the row/column contain constants or formulas or both?

 

[Human_doing]

Hi,

Sorry to confuse, yes it is row 1 I am looking to select. What I'm trying to achieve is to select all the header cells (i.e. cells in row 1) and turn them grey as part of a wider report code,

Thanks

 

[MrKowz]

Try:

Dim LC as Long
LC = Cells(1,Columns.Count).End(xlToLeft).Column
Range(Cells(1,1),Cells(1,LC)).Interior.ColorIndex = 15

 

[Peter_SSs]

.. or if there happens to be gaps and you don't want the gaps coloured, or the headers don't start in column A then perhaps this?

Rows(1).SpecialCells(xlCellTypeConstants).Interior.ColorIndex = 15

 

[Weaver]

You could try something like this, which will only shade the cell if it has a non formula value in it.

Sub mcro()
    With ActiveSheet.Range("1:1").SpecialCells(xlCellTypeConstants)
        .Interior.ColorIndex = 15
    End With
End Sub

 

[Human_doing]

Thanks for your help guys yes that works,

Thanks

 

[iyermobile]

Sub macFilterDuplicates()
'
' macFilterDuplicates Macro
'

'

Cells(1, 1).Select

While (Not (IsNull(ActiveCell.Value2) Or IsEmpty(ActiveCell.Value2)))
MsgBox ActiveCell.Value2
Application.CutCopyMode = False

Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select
Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:= _
xlYes

Cells(Selection.Row, Selection.Column).Offset(, 1).Select
Wend
End Sub