Edit every worksheet in a workbook

Uma Chauhan

New Member
Joined
Jul 30, 2019
Messages
7
Hi Guys,

I am trying to run a piece of code in all the sheets in a workbook. However my code is only editing first sheet and then courses through other sheets but does not edit them. PFB the code:

Sub Worksheet_Editor()



For Each Worksheet In ThisWorkbook.Sheets


Cells.Select
Selection.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Next

ActiveWorkbook.Save


End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
349
Office Version
2016
Platform
Windows
Hi Guys,

I am trying to run a piece of code in all the sheets in a workbook. However my code is only editing first sheet and then courses through other sheets but does not edit them. PFB the code:

Sub Worksheet_Editor()



For Each Worksheet In ThisWorkbook.Sheets


Cells.Select
Selection.Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Next

ActiveWorkbook.Save


End Sub
Try this.
Code:
Sub Worksheet_Editor()

Dim Worksheet As Worksheet

For Each Worksheet In ThisWorkbook.Sheets

    With Worksheet.UsedRange
    
        .Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    
    End With

Next

ActiveWorkbook.Save

End Sub
 

Uma Chauhan

New Member
Joined
Jul 30, 2019
Messages
7
I got an error 1004 for the line :

.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

There is nothing wrong with the actual code. The only problem is it is not going through all the sheets. I need it to run in all sheets in workbook
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
349
Office Version
2016
Platform
Windows
I got an error 1004 for the line :

.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

There is nothing wrong with the actual code. The only problem is it is not going through all the sheets. I need it to run in all sheets in workbook
I slightly edited your code to not require the use of Select. It works on my end so the error you are getting would just mean that there were no blank cells found. Place "on error resume next" before the loop starts.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,786
Members
406,721
Latest member
Laiceyshae

This Week's Hot Topics

Top