Link to home
Create AccountLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

Delete Rows

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
Avatar of Norie
Norie

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

Avatar of Cartillo

ASKER

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.  
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.  
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.
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.  
Sorry I don't understand.

Perhaps you should start a new question with more details.
Hi,

Thanks a lot for the help.