Return range of non blank cells in a row

[vbaNewby]

Hello all,

Fairly new to VBA, I have for example ( using "_" character to represent blank )


_ _ _ _ 2 5 3 9 0 _ _ _ _


What I want to do in VBA is return the range of the first row of only the cells that have data. In this example I would return the range of E1:I1

I've tried various examples off the web but I can't quite find what I am looking for.

Also note that I want to specify which row to return the range of populated values in. Row 1 in this case. Can any guru's please provide some help!???

Thanks in advance!

 

[Andrew Poulsom]

Welcome to the Board.

Try (for row 1):

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
        Set Rng = Range(Rng, Rng.End(xlToLeft))
        MsgBox Rng.Address
    End With
End Sub

 

[MickG]

Try:-
To run code "Double click" any Cell/any row.

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
 [COLOR=navy]Set[/COLOR] Rng = Range(Cells(Target.row, 1), Cells(Target.row, Columns.Count).End(xlToLeft))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Not Dn = "" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Dn
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]Set[/COLOR] nRng = Union(Dn, nRng)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]If[/COLOR] Not nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
    MsgBox "Row = " & Target.row & Chr(10) & "Data Range = " & nRng.Address
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[vbaNewby]

Welcome to the Board.

Try (for row 1):

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Cells(1, .Columns.Count).End(xlToLeft)
        Set Rng = Range(Rng, Rng.End(xlToLeft))
        MsgBox Rng.Address
    End With
End Sub

Thank you for the worm welcome and timely respone, Andrew. Worked like a charm.