Solved

Clear row in Excel

Posted on 2010-08-13
17
570 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Wilder1626
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 7

Expert Comment

by:bouscal
ID: 33434342
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 33434372
Yes, that is correct for the line to delete.

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

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33435501
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33435523
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
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 33435547
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 33435909
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33435925
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
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 33436051
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 33436102
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 33436181
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33437670
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 33438501
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 33438551
change

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

to

finis = 11
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33438614
By the way, how many lines would a typical sheet contain? Is is within a few hundred or is it more?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33440211
It should always be less then 200
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 33440215
The given code should work fine. Did you try it?
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 33440344
That is perfect and a easy to understand code.

Thanks a lot.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now