Macro using end(xlDown) and End(xlToRight))

[rlkerr]

I have a macro that I found and I need to make some changes. However, I'm not that familiar with VBA so I need some help.


What I need it to do is select range A1 end right and range end down. Maybe like this : "If Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select
I don't know how to put it in the code below.

Goal is to take all the 0 and blank cells and clear them.

Sub Clear_Content()
    
    If VarType(Range("B1")) = vbEmpty Then
        Range("A1").ClearContents
    ElseIf VarType(Range("B1")) = vbString Then
        If Len(Range("B1")) = 0 Then
            Range("A1").ClearContents
        End If
    End If
End Sub

[bakerman2]

How about this.

    lCol = Range("A1").End(xlToRight).Column
    lRow = Range("A1").End(xlDown).Row
    Range("A1").Resize(lRow, lCol).Select

[rlkerr]

How do I incorporate it into my Macro. I need a Dim statement or something? Can you put it in the statement for me?

[jolivanes]

No selecting needed.
You indicate to clear, not delete, cells with a zero (0). I don't think you have to worry about clearing blank cells.

One possibility:
This will replace all zeros on the whole sheet.

Sub Replace_Zeros()
    With Sheets("Sheet2").UsedRange    '<---- Change as required
        .Replace 0, "", xlWhole
    End With
End Sub

If you want to delete cells, maybe up or left, that would be a different story


This will delete the Cells with a Zero (0) and shift up.

Sub Delete_Cells_Up()
Dim lc As Long, lr As Long, i As Long, j As Long
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
    For j = 1 To lc
        For i = lr To 1 Step -1
            If Cells(i, j).Value = 0 Then Cells(i, j).Delete Shift:=xlUp
        Next i
    Next j
Application.ScreenUpdating = True
End Sub

This will delete the Zeros and shift blank Cells up.

Sub Delete_Zeros_And_Blanks()
Application.ScreenUpdating = False
    With Sheets("Sheet2").UsedRange    '<---- Change as required
        .Replace 0, "", xlWhole
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    End With
Application.ScreenUpdating = True
End Sub

[rlkerr]

Still not quite there. It maybe simpler to just delete all lines below the last line. I have attached an example. I just need all the blank lines below the data to be deleted.

[rlkerr]

I posted an example spreadsheet.

[bakerman2]

Try this.

Sub tst()
    With Sheet1
        For i = 1 To .Rows.Count
            If .Cells(i, 1) = "" Then lrow = i: Exit For
        Next
    .Range("A" & i, .Range("A" & .Rows.Count).End(xlUp)).Resize(, 18).Clear
    End With
End Sub