VBA help needed urgently

[asad]

Hi Guys,

I have recorded the following macro

Sub Macro1()    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").Select
    Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("$A$1:$C$449").AutoFilter Field:=3
    Range("D2").Select
End Sub

Can I please get this to work on all the sheets in the workbook?

Thanks for your help
Asad

 

[asad]

I tried the following but does not work

Sub Macro1()
Dim ws As Worksheet
For Each ws In ThisWorkbook
ws.Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1").Select
    Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("$A$1:$C$449").AutoFilter Field:=3
    Range("D2").Select
Next ws
End Sub

 

[Michael M]

MAybe this...are you trying to delete all rows with formuas in col "C" ??

Sub Macro1()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
    With Cells
    .Value = .Value
    End With
    Range("C1").Select
    Range("$A$1:$C$500").AutoFilter Field:=3, Criteria1:="="
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("$A$1:$C$449").AutoFilter Field:=3
    Range("D2").Select
Next ws
End Sub

 

[MARK858]

MAybe this...are you trying to delete all rows with formuas in col "C" ??

I would think the OP is trying to delete the rows/range with blank cells in column C with the criteria "=".

 

[Michael M]

Hi Mark
My assumption was looking for an "=" sign in the cell ???
I guess we'll find out when the OP responds...

 

[asad]

Thanks a lot guys.
Yes, I am trying to delete the rows with blank cells in column C.
Orginally, the column B and D and E have formulas. But now, I need to delete the rows with no values.

 

[Michael M]

UNTESTED

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Replace "", "#N/A", xlWhole, , False, , False, False
    Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
Next ws
End Sub

 

[MARK858]

@asad, you state that "Orginally, the column B and D and E have formulas." but what about column C, is column C constants with genuine blank cells or formulas returning ""?

If it is genuine blank cells then all you probably need is...

Sub Macro2()
    Dim ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        ws.Range("C3:C" & ws.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12).EntireRow.Delete
    Next

    Application.ScreenUpdating = True
End Sub

Please note that the above assumes that column A is the longest column with data.

 

[asad]

Thanks a lot guys.
I will try these solutions. As I have many files each with more than 100 tabs to be cleaned. So I have plenty of testing grounds.
I will let you all know tomorrow with the results.
Thanks
Asad

 

[MARK858]

Just in case any of the sheets don't have any blanks in column C....

Sub Macro2()
    Dim ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
    On Error Resume Next
        ws.Range("C3:C" & ws.Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12).EntireRow.Delete
    On Error GoTo 0
    Next

    Application.ScreenUpdating = True
End Sub