[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Clear row in Excel

Posted on 2010-08-13
17
Medium Priority
?
613 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

649 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