Need to loop this macro to all sheets in the workbook. Which works for 1 sheet.

Dim sh As Worksheet

For Each sh As ThisWorkbook.Sheets

With sh
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="<>*Visual*"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
End With

Next

hrh

Dave

Sub x()

Dim rData As Range
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
With ws
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="<>*Visual*"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
End With
Next ws

Application.ScreenUpdating = True

End Sub

Select allOpen in new window

xovers...

Dave, I see I've written some syntax wrong because of not using Excel :-)

In the For loop, "As" should be "In" :-)

Hello bsharath,

the code below will loop through all sheets in the current workbook and will run the macro on each sheet.

cheers, teylyn

Sub x()

Dim rData As Range
dim ws as worksheet

Application.ScreenUpdating = False

for each ws in ThisWorkbook.Worksheets
With ws
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=3, Criteria1:="<>*Visual*"
    With .AutoFilter.Range
        On Error Resume Next
        Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rData Is Nothing Then
            rData.EntireRow.Delete
        End If
    End With
    .AutoFilterMode = False
End With

next ws
Application.ScreenUpdating = True

End Sub

Select allOpen in new window

ouch, Dave and Harish,

slight time and posting delay b/c NZ network sluggishness.... Excuse the double-up.

cheers, teylyn

... and patrickab, too. Man, I wish NZ were connected to the world in real time!

Thank you all. Checking on it...any help on this
https://www.experts-exchange.com/questions/26270808/Check-colum-A-for-names-in-the-name-matches-a-sheet-name-in-any-workbook-then-copy-to-the-sheet-where-we-are-quering-from.html

teylyn,

I reckon that NZ is about the same speed of connection that I get in my neck of the woods in the UK. My connection is at times so slow that I am currently considering setting up a local project to significantly improve internet speeds in our road - we're not cabled so we all use copper wire links. At times it is awful as I reckon the contention ratio is poor, couple that with a significant distance to the exchange and we end up with a pretty poor service.

Patrick

Patrick, I feel your pain.

I'm now on broadband, with a half-way decent download speed, but two years ago, we were in an area that absolutely could not get broadband and will not get it for the next couple of dozen years. One reason to move location was to get better than dial-up internet.

I still balk at 500 Kb+ downloads, and when I see askers post 10MB attachments, I close the question faster than you can say "Megabyte".

I've successfully done web development of rich AND fast web sites before Web 2.0 and I know it is still possible to convey interesting and meaningful content without bursting the traffic caps.  Unfortunately, a lot of people don't know the difference between a 3 MB BMP and a 15 Kb GIF that look exactly the same.

Maybe EE should have a Screenshot 101 and a section about how to prepare small data samples that illustrate the askers' issues.

bsharath, sorry for hijacking your question.

cheers, teylyn

teylyn,

I usually don't bother with questions that include files larger than 500kB. They take too long to download and use too much of my bandwidth allowance (5GB/month). I am also unable to consider uploading largish photo projects (1.5GB) that fail in my DVD creation software so that the software company can tell me what the problem is. I'm a bit fed up with all of that so I am investigating what can be done to drastically improve speeds.

I'm hoping my neighbour will get involved as comms are his speciality - I'll be contacting him later today.

Meantime, apologies Sharath for intruding in your question.

Patrick

Hi

I get this

---------------------------
Microsoft Excel
---------------------------
Excel cannot complete this task with available resources.  Choose less data or close other applications.
---------------------------
OK  
---------------------------

Seems like your computer has a capacity problem. This has nothing to do with Excel, but rather with the processing power and/or memory of your computer. Close all applications, reboot, start only Excel and the file in question and run the macro. If that does not work, you may want to upgrade your computer to be grunty enough for the job.

cheers, teylyn

:-)
My system is a Quad processor 12 GB ram
Let me restart the machine check and get back

After the error also i get the same erro after that i get the below error
When debug goes here
            rData.EntireRow.Delete

Error as below
Capture.JPG

Which of the suggestions above are you using?

I get the same error for your code,Dave & partick's

The code I posted runs without error on my Excel 2003 in Windows XP. Of course, I don't have your data set, so can't test against your file. In my mocked up data set, the rows were deleted without error.

Maybe you can post a file with some sample data and specify the version of Excel you are using.

cheers, teylyn

I think i found the issue. if there is a empty sheet it errors.

Sharath - Thanks for the points - Patrick