Excel VBA: For Each ws loop doesn't work as expected

Drakienz

New Member
Joined
Jun 22, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi, new to this forum, hope you guys can help me with my issue altho my expressions are terrible :')

I have attached a picture of how my excel looks like and my code below.
Basically what it's supposed to do is to extract files from different locations and combine them as separate worksheets in one workbook.
Then I want to remove all rows that contains "Outpayment" in column C and all rows that starts with 9 in column G.

Everything works fine until I realise that the code ".Replace "9*", "#N/A", xlWhole, , False" also removes rows that contain "1991" in column G, but this only happens in the Jan22 and Feb22 sheets, not the Mar22 sheet.

Then I tried inputting this code:
"With ws.UsedRange
Dim lngLastRow As Long, lngRow As Long

lngLastRow = Range("G" & Rows.Count).End(xlUp).Row

For lngRow = lngLastRow To 2 Step -1
If Left(Range("G" & lngRow), 1) = "9" Then Rows(lngRow).Delete
Next lngRow
End With"

This removes the rows that starts with 9 in Jan22 and Feb22 sheets but doesn't remove any rows in Mar22 sheet.

Appreciate any help I can get, thank you!

VBA Code:
Sub SET05R()

Application.ScreenUpdating = False

Dim wbDest As Workbook
Dim MtName As String
Dim MtPath As String
Dim wbsName As String

Application.DisplayAlerts = False

MtName = Range("G5")
MtPath = Range("G6")
Set wbDest = Workbooks.Add
wbDest.SaveAs Filename:=MtPath & MtName, FileFormat:=xlNormal

Application.DisplayAlerts = False

ThisWorkbook.Activate

Dim wbSc As Workbook
Dim FullPath As String

Const sPath As String = "X:\Carrier Services\IAS\System Reports\SingTel\Month-End\Year"
FullPath = sPath & Range("B1") & "\"
Scol = 9

Do While Scol > 6

ThisWorkbook.Activate
Set wbSc = Workbooks.Open(FullPath & Cells(4, Scol) & "\" & Cells(7, Scol) & Range("J8"))
wbSc.Activate
ActiveSheet.Copy Before:=Workbooks(MtName).Sheets(1)
wbSc.Close False

Scol = Scol - 1

Loop

Workbooks(MtName).Activate
Sheets("Sheet1").Delete
ActiveWorkbook.Save

Dim ws As Worksheet

On Error Resume Next
For Each ws In Sheets
    With ws.UsedRange
      .Replace "Outpayment", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
    
    With ws.UsedRange
      .Replace "9*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
    
    ws.Activate
    With Application.ActiveWindow
        Rows("2:2").Select
        .FreezePanes = True
    End With
    With ws
        Range("B:D").EntireColumn.AutoFit
        Range("F:G").EntireColumn.AutoFit
        Range("J:Q").EntireColumn.AutoFit
    End With
Next ws

Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = False

End Sub
 

Attachments

  • set05 macro.png
    set05 macro.png
    40.8 KB · Views: 11

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Another thing to add on, I am also getting an error when attempting to reopen the file.
 

Attachments

  • Screenshot 2022-06-22 155622.png
    Screenshot 2022-06-22 155622.png
    6.4 KB · Views: 11
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top