Help tweaking this code from macro recorder so it works correctly with my code: (pretty simple stuff)

[kbishop94]

the code simply needs to find the first empty cell in row 1 (starting with column H(8), and going over to the last column that has text in it, then plus 1 column), and then from that point select all the way down, and all the way to the right. Then clear all, then delete.
I have it working fine where it finds the correct cell/column, but I cannot get what the macro I used as a basis to delete everything from that point (which happens to be column "OF") and revise it so that instead of column "OF", it should select the last last column with text in it, +1 column: (my variable "rCell" would be this column.)

Here is my code:

Private Sub cmdLastRow_Click()
Dim rCell As Range
Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count

For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))
If rCell = "" Then

    [B][COLOR=#0000cd]Columns("OF:OF").Select[/COLOR][/B]

    Range(Selection, Selection.End(xlToRight)).Select
    Selection.clear
    Selection.Delete Shift:=xlToLeft
Else
End If
Next rCell

End Sub

So I need the:

[B][COLOR=#0000CD]Columns("OF:OF").Select[/COLOR][/B]

part to be changed so that its actually column "lCol +1" (or rCol) and not the "OF:OF" which is from my macro I used as the basis to get the clearing/deleting functions.

Please and Thank you for any assistance.

 

[Michael M]

Try using

    Columns(1).Resize(, lCol + 1).EntireColumn.Select

 

[kbishop94]

Try using

    Columns(1).Resize(, lCol + 1).EntireColumn.Select

Thank you, Michael M.

Using the original "macro" code I started with (and omitting the clear/delete code so it just selects the intended area) it selects the correct column.

But using your supplied code I see this:

and then when the clear/delete code is added obviously its deleting everything on the worksheet. Thank you, I do appreciate your input, but how can i make it so it goes the other way (away from A1 and out towards to the right and out to infinity? (the end of the sheet, I mean ) Please and Thank you, sir.

 

[kbishop94]

I got it, Michael.

For Each rCell In Range(Cells(1, 8), Cells(1, lCol + 1))
If rCell = "" Then

[COLOR=#0000cd][B]Columns.EntireColumn(lCol + 1).EntireColumn.Select[/B][/COLOR]

    Range(Selection, Selection.End(xlToRight)).Select
    Selection.clear
    Selection.Delete Shift:=xlToLeft

Else
End If
Next rCell