Combining 2 Macro's

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,776
Office Version
  1. 2019
Platform
  1. Windows
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



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
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
Another one is you need to be careful with DOT. Your previous one
VBA Code:
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

Before Cells(r, 1), you forget the DOT. So, the Cells can refer to any selected sheet at that time and it was on 2nd sheet. The .Rows(r).Delete refers to Worksheets(I) ;)
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
Many thanks for your input and code-its much appreciated

I can now see where I went wrong
Glad could help. If you have not provided the file, I cannot find the problem fast :)
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,776
Office Version
  1. 2019
Platform
  1. Windows
I agree much better to attach files when coding is not straightforward and one needs to see the files
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,773
Members
416,939
Latest member
Rajakumaran

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
Top