We help IT Professionals succeed at work.

Delete Rows

Cartillo
Cartillo asked
on
Hi Experts,

I would like to request Experts help create a macro to delete rows at Week1 to Week5 sheets if the cells at column B to H are empty. Hope Experts will help me crate this feature. Attached the workbook for Experts perusal.
Delete-Rows.xls
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Try this.
Option Explicit

Sub GetRidOfPeskyBlanks()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim I As Long
Dim J As Long
    Application.ScreenUpdating =False
    For I = 1 To 5
        Set ws = Worksheets("Week" & I)
        
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        For J = LastRow To 3 Step -1
        
            Set rng = ws.Range("A" & J)
            
            If Application.WorksheetFunction.CountBlank(rng.Offset(, 1).Resize(, 7)) = 7 Then
                rng.EntireRow.Delete xlShiftUp
            End If
        Next J
    Next I
    Application.ScreenUpdating =True
End Sub

Open in new window

Author

Commented:
Hi imnorie,

Thanks for the code. Is that a way for me to revert the Week sheets back to default time set (the actual format before executing the deleting macro)? This is essential when I intent to repeat the same function with a different set of data.  
Analyst Assistant
CERTIFIED EXPERT
Commented:
Not really.

The first thought I had was why you would want to do this, as it does kind of changes things quite bit.

I suppose you could hide instead of delete.

Yeah, that seems to work.
Option Explicit

Sub GetRidOfPeskyBlanks()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim I As Long
Dim J As Long

    For I = 1 To 5
        Set ws = Worksheets("Week" & I)
        
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        For J = LastRow To 3 Step -1
        
            Set rng = ws.Range("A" & J)
            
            If Application.WorksheetFunction.CountBlank(rng.Offset(, 1).Resize(, 7)) = 7 Then
                rng.EntireRow.Hidden = True
            End If
        Next J
    Next I

End Sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT
Commented:
You'll want something to make them visible again I suppose.
Sub UnhidePeskyRows()

Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim I As Long
Dim J As Long

    For I = 1 To 5
        Set ws = Worksheets("Week" & I)
        
        LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
        For J = LastRow To 3 Step -1
        
            Set rng = ws.Range("A" & J)
            

                rng.EntireRow.Hidden = False
                
        Next J
    Next I
End Sub

Open in new window

Author

Commented:
Hi imnorie,

Cool! is that a possible when we perform unhide macro, the whole data at B4:H279 are deleted and also clear all color (highlight) at this range. Hope you will consider with this request.  
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Sorry, I don't follow.

You don't want to delete anything so hiding rows is fine, but when you unhide the rows you want to delete all the data?

If you just want to start fresh with a new sheet without data you should create a template sheet which only needs the dates filled in.

Then have code to create a new sheet from the template and replace the original.

Author

Commented:
Hi imnorie,

When I unhide the rows, meaning  I intent to copy a new data in this cells. That the main reason I want to delete the old data.  
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Sorry I don't understand.

Perhaps you should start a new question with more details.

Author

Commented:
Hi,

Thanks a lot for the help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.