Clear row in Excel

To verify I understand  your request.  In your attached file you would like rows 13, 14, 15,16,18, and 19 to be deleted, is that correct?

Also, are you using the Cells.Find() to locate the last cell in your worksheet?

Yes, that is correct for the line to delete.

The code that i was using is above Cells.Find()

Thanks

If VBA is not a must then you can easily do this by the following method

apply data filter to the entire range   data > filter > autofilter
from the dropdown in column G select blanks
select the filtered rows
delete the rows
remove data filter


Saqib

unless I completely misunderstood you this code should do what you have asked

Private Sub CommandButton1_Click()
strt = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = strt To finis Step -1
        If Cells(i, 7) = "" Then Cells(i, 7).EntireRow.Delete
Next i
End Sub

Try this code...

Private Sub CommandButton1_Click()
'loop in reverse to avoid row mismatch after deletion
For i = Cells.SpecialCells(xlCellTypeLastCell).Row To 2 Step -1
    'first check if G column has data
    'my understanding is that you need to delete row if:
        '1)Column G is empty
        '2)Any other column has data
        
    If Len(Trim$(Range("$G$" & i).Value)) = 0 Then
        'you have 55 columns?
        For j = 1 To 55
            'if not G Column
            If j <> 7 Then
                'if cell is not empty
                If Len(Trim$(Cells(i, j).Value)) > 0 Then
                    'delete row and shift below row upward
                    Rows(i).Delete Shift:=xlUp
                    GoTo nextI
                End If
            End If
        Next j
    End If
nextI:
Next i
End Sub

Select allOpen in new window

all of the code are very good but the only problem that i see is that the color in cells needs to stay in all the line on that excel spreadsheet. and not delete a row.

If i have 30 rows with colors in cells, if a delete data because there's no data in column G, it will clear the data on that row and if the other row does, it will bring the data up of one line without deleting a row.

ok then try this

Private Sub CommandButton1_Click()
strt = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = strt To finis Step -1


        If Cells(i, 7) = "" Then
            For j = i To strt
            Range(Cells(j, 1), Cells(j, 55)).Value = Range(Cells(j + 1, 1), Cells(j + 1, 55)).Value
            Next j
        End If


Next i


End Sub

I think if you use a combination of 2 simple functions this will work very nicely. I hope this is what you need.

(NOTE: I have used [.UsedRange] here to limit the number of rows in the target sheet that I look at, for simplicity during testing, but this property is not allways reliable so you may want to replace it with another method of finding the used area / rows)

Regards,

Sub emptyMarkedRows()

    Dim wksTarget As Worksheet
    
    Dim rngCell As Range
    
    Dim lngRow As Long
    
    Set wksTarget = Worksheets(2)
    
    ' If there is a value in column G then clear the data in that row
    For lngRow = 1 To wksTarget.UsedRange.Rows.Count
    
        ' Check column G
        If IsEmpty(wksTarget.Cells(lngRow, 7)) Then wksTarget.Rows(lngRow).ClearContents
        DoEvents
        
    Next
    
    ' Delete blank rows
    deleteBlankRows wksTarget
    
    Set wksTarget = Nothing
    
End Sub

Sub deleteBlankRows(wksTarget As Worksheet)

    Dim lngRow As Long
    
    Dim rngToBeDeleted As Range


    
    ' Find all blank rows
    For lngRow = 1 To wksTarget.UsedRange.Rows.Count
        
        If blnRangeIsBlank(wksTarget.Rows(lngRow)) Then
            If rngToBeDeleted Is Nothing Then
                Set rngToBeDeleted = wksTarget.Rows(lngRow)
            Else
                Set rngToBeDeleted = Union(rngToBeDeleted, wksTarget.Rows(lngRow))
            End If
        End If
    Next
    
    ' Delete the rows
    rngToBeDeleted.Delete
    
    Set rngToBeDeleted = Nothing

End Sub

Select allOpen in new window

In that case, just change

Rows(i).Delete Shift:=xlUp

in my code to...

Rows(i).ClearContents


I remember you had Clear earlier. If I am not mistaken, "Clear" clears everything, including formatting.

If i use this code:
strt = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = strt To finis Step -1


        If Cells(i, 7) = "" Then
            For j = i To strt
            Range(Cells(j, 1), Cells(j, 55)).Value = Range(Cells(j + 1, 1), Cells(j + 1, 55)).Value
            Next j
        End If


Next i



Can i make him start at row 11 for that macro

I am not sure what you mean. For the given file the loop starts from row 21 and works its way to row 11 meaning in reverse order. You can fix it by setting finis to 11 instead of the given formula.

Saqib

Cause in my official spreadsheet, i have title from row 1 to 10 and when i click on the macro, it also delete them cause in column G, theres nothing.

By the way, how many lines would a typical sheet contain? Is is within a few hundred or is it more?

It should always be less then 200

The given code should work fine. Did you try it?

That is perfect and a easy to understand code.

Thanks a lot.