I have two macro's-One to import multiple Files and the second macro to delete blank rows on all sheets in Col A
I have incorporated the second macro ("Delete_Rows_apostrophe") with the first macro Open_MultipleFiles, but when doing this the second macro does not delete the apostrophes (Blank cells)
When I run Sub Delete_Rows_apostrophe() epertely , it works perfectly
I cannot determine why it will not work
It would be appreciated if someone could kindly assist me
I have incorporated the second macro ("Delete_Rows_apostrophe") with the first macro Open_MultipleFiles, but when doing this the second macro does not delete the apostrophes (Blank cells)
When I run Sub Delete_Rows_apostrophe() epertely , it works perfectly
I cannot determine why it will not work
It would be appreciated if someone could kindly assist me
Code:
Sub Open_MultipleFiles()
'ChDir "C:\downloads\"
Application.ScreenUpdating = False
Dim LR As Long
Application.DisplayAlerts = False
With Sheets("Sales Data")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LR).ClearContents
End With
With Sheets("report Excluding Zero Values")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:C" & LR).ClearContents
End With
Dim fDialog As Object, varFile As Variant
Dim nb As Workbook, tw As Workbook, ts As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.CutCopyMode = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set fDialog = Application.FileDialog(3)
'ChDir "C:\downloads"
With fDialog
.Filters.Clear
.Filters.Add "Excel files", "*.xlsm*"
.Show
For Each varFile In .SelectedItems
Set nb = Workbooks.Open(Filename:=varFile, local:=True)
With Sheets("Sales Data")
.Range("A1:C1000").Copy
ThisWorkbook.Sheets("Sales Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats
ThisWorkbook.Sheets("Sales Data").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
With Sheets("report Excluding Zero Values")
.Range("A1:C1000").Copy
ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormats
ThisWorkbook.Sheets("report Excluding Zero Values").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
nb.Close False
Next
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.CutCopyMode = True
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Delete_Rows_apostrophe
End Sub
Sub Delete_Rows_apostrophe()
Dim I As Long
Dim r As Long
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For r = LR To 1 Step -1
For I = 1 To Worksheets.Count
With Worksheets(I)
If Cells(r, 1) = "" And Not IsEmpty(Cells(r, 1)) Then
.Rows(r).Delete
End If
End With
Next I
Next r
End Sub