Delete same row from multiple workbooks (files)

topi1

Active Member
Joined
Aug 6, 2014
Messages
252
Office Version
  1. 2010
Hi, I know how to delete the same row number from all worksheets but need help to remove row numbers 3 from all open workbooks. There are about 30 workbooks. Each has one sheet but ideally, I would like the vba to work even if some workbooks contained more than one sheet. For vba to work, I am happy to open all workbooks in question or put them in one folder. Thank you in advance.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Put all your files in one folder. Place this macro in a regular module in a blank (or another) workbook and run it from there. Change the folder path (in red) to suit your needs.
Rich (BB code):
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim WB As Workbook, ws As Worksheet
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set WB = Workbooks.Open(strPath & strExtension)
        For Each ws In Sheets
            ws.Rows(3).Delete
        Next ws
        WB.Close True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Put all your files in one folder. Place this macro in a regular module in a blank (or another) workbook and run it from there. Change the folder path (in red) to suit your needs.
Rich (BB code):
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim WB As Workbook, ws As Worksheet
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set WB = Workbooks.Open(strPath & strExtension)
        For Each ws In Sheets
            ws.Rows(3).Delete
        Next ws
        WB.Close True
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
@mumps Thank you so much. Changed the address of the folder and changed .xlsx to .csv since the excel files were .csv.
The macro never stopped and excel icon in the taskbar kept flashing and the folder showed files constantly changing. I finally killed it with Ctrl+Alt+delete. When I opened the files, rows 3 were deleted but row 4s were also deleted. (The files had 4 rows. I wanted to keep 1,2,and 4. Thank you.
 
Upvote 0
Could you upload 2 or 3 of the csv files to DropBox and post the links to the files here so I can test the macro?
 
Upvote 0

Forum statistics

Threads
1,224,837
Messages
6,181,255
Members
453,028
Latest member
letswriteafairytale

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