Clear row in Excel

Hello all,

I would like to modify that code with these specification:

I need to keep all colors in cells. But if there's no data in column G but some or all other cells in the same row have data, it will clear all data in the row but at the end of the code, if there a empty row between 2 rows with data, the one after the empty row will go up 1 row.

How can i do that?

Thanks for your help
For i = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If i = 1 Then
        i = 2
    End If
               For j = 1 To 55
                      If j = 7 Then
                          GoTo nxt1
                      End If
                      If Cells(i, j).Value <> "" Then
                            Rows(i).Delete
                            i = i - 1
                            GoTo nxt2
                      End If
nxt1:
                Next j
nxt2:
Next i

Open in new window

test-delete.xls
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimBusiness Systems AnalystCommented:
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?
0
Wilder1626Author Commented:
Yes, that is correct for the line to delete.

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

Thanks
0
Saqib Husain, SyedEngineerCommented:
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
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Saqib Husain, SyedEngineerCommented:
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
0
Shahid ThaikaSole ProprietorCommented:
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

Open in new window

0
Wilder1626Author Commented:
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.
0
Saqib Husain, SyedEngineerCommented:
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
0
AL_XResearchCommented:
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

Open in new window

0
Shahid ThaikaSole ProprietorCommented:
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.
0
Wilder1626Author Commented:
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
0
Saqib Husain, SyedEngineerCommented:
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
0
Wilder1626Author Commented:
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.
0
Saqib Husain, SyedEngineerCommented:
change

finis = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

to

finis = 11
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
By the way, how many lines would a typical sheet contain? Is is within a few hundred or is it more?
0
Wilder1626Author Commented:
It should always be less then 200
0
Saqib Husain, SyedEngineerCommented:
The given code should work fine. Did you try it?
0
Wilder1626Author Commented:
That is perfect and a easy to understand code.

Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.