Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-02-2017, 10:03 AM
Alimou Alimou is offline Macro to delete rows not containing specific text Windows 8 Macro to delete rows not containing specific text Office 2013
Novice
Macro to delete rows not containing specific text
 
Join Date: Jan 2016
Posts: 4
Alimou is on a distinguished road
Default Macro to delete rows not containing specific text

Hi,
I'm new to the forum...

Would anyone help me with a macro that:
1. Loops through all sheets in a workbook


2. Delete all rows in each sheet which don't contain 'Country', 'Spain' and "" (empty spaces) - The column to look for the values is column B in all sheets
3. Save the workbook as 'Spain.xls'

Many thanks

Ali
Reply With Quote
  #2  
Old 02-02-2017, 06:12 PM
ThisGuyJohn ThisGuyJohn is offline Macro to delete rows not containing specific text Windows 7 64bit Macro to delete rows not containing specific text Office 2016
Novice
 
Join Date: Jan 2017
Posts: 6
ThisGuyJohn is on a distinguished road
Default

When you run this it will delete all the rows with blank spaces country and spain

Code:
Sub Filter()
    Dim b As Long
    With ActiveSheet
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)
                
                    If Not .Range("b1").Value = "Country" Then
                    If Not .Range("b1").Value = "Spain" Then
                    If Not .Range("b1").Value = vbNullString Then
                        .Delete
                    End If
                    End If
                    End If
                
            End With
        Next b
    End With
    
End Sub
Reply With Quote
  #3  
Old 02-03-2017, 05:34 AM
Alimou Alimou is offline Macro to delete rows not containing specific text Windows 8 Macro to delete rows not containing specific text Office 2013
Novice
Macro to delete rows not containing specific text
 
Join Date: Jan 2016
Posts: 4
Alimou is on a distinguished road
Default

Thank you TGJ - I will give it a try.

What if I want this macro to loop through all existing worksheets except for the sheet named 'Frontpage'?
Reply With Quote
  #4  
Old 02-03-2017, 02:28 PM
ThisGuyJohn ThisGuyJohn is offline Macro to delete rows not containing specific text Windows 7 64bit Macro to delete rows not containing specific text Office 2016
Novice
 
Join Date: Jan 2017
Posts: 6
ThisGuyJohn is on a distinguished road
Default

On the top of the code where it says With ActiveSheet

Code:
Sub Filter()
    Dim b As Long
    With ActiveSheet
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)
just add With "what ever the worksheet is called" - without the quotes
for example

With sheet2
With sheet3

then you need to add "end with" for each "with" you add I put in on the bottom

for Ex:

Code:
Sub Filter()
    Dim b As Long
    With ActiveSheet
With sheet2
With sheet3
With sheet4
With sheet5
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)
                
                    If Not .Range("b1").Value = "Country" Then
                    If Not .Range("b1").Value = "Spain" Then
                    If Not .Range("b1").Value = vbNullString Then
                        .Delete
                    End If
                    End If
                    End If
                
            End With
        Next b
    End With
End With
End With
End With
End With
    
End Sub
Reply With Quote
  #5  
Old 02-06-2017, 07:35 AM
Alimou Alimou is offline Macro to delete rows not containing specific text Windows 8 Macro to delete rows not containing specific text Office 2013
Novice
Macro to delete rows not containing specific text
 
Join Date: Jan 2016
Posts: 4
Alimou is on a distinguished road
Default

Hi TJG,
I made the amendments and it worked. Thank you very much for your help.

A.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to delete rows not containing specific text Macro to delete text in specific styles ljd108 Word VBA 14 01-22-2019 01:22 PM
Macro to delete rows not containing specific text Macro to delete text from cells with specific format Soenke Word VBA 4 09-01-2016 08:55 AM
Macro to delete rows not containing specific text Macro to delete all empty rows from all tables braddgood Word VBA 15 10-02-2015 01:54 PM
Macro to delete rows not containing specific text Macro to conditionally delete rows Steve_D Excel 2 08-24-2012 09:37 PM
Macro to delete rows not containing specific text Macro to delete rows with all empty cells ubns Excel Programming 2 08-14-2012 02:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft