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!
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