VBA code to Delete multiple rows example will help us to delete multiple rows from excel worksheet. We can use Delete method of Rows to delete the multiple rows. In this example we will see how to delete the multiple rows in excel worksheet using VBA. VBA code for deleting multiple rows macro should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.
VBA code to delete multiple rows
Here is the Example VBA syntax and Example VBA Macro to delete multiple rows from excel worksheets. This will help you to know how to delete specific and multiple rows from Excel workbook using VBA.
VBA Delete multiple rows: Syntax
Following is the VBA Syntax and sample VBA code to delete multiple rows from worksheet using VBA. We are using the Delete method of the Rows object of worksheet.
Here Row Numbers are your row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet. Now you can observe that the entire rows from 1 to 3 are deleted from worksheet. ‘Specifying the Rows to delete and Deleting the Rows using EntireRow.Delete method. ‘Ending the sub procedure to delete entire row Here Rows(“1:3”) is to tell excel to delete rows from 1 to 3 of the worksheet. And Delete method will delete the all specified rows form the worksheet.
Rows(“
Delete multiple rows using VBA: Examples
The following VBA code is to delete multiple rows from the worksheet. This code will delete the multiple rows (1 to 3) which we have mentioned in the code.
Sub sbVBS_To_Delete_Multiple_Rows ()
Rows(“1:3”).EntireRow.Delete
End Sub
Instructions to run the VBA code to delete multiple rows
Please follow the below steps to execute the VBA code to delete multiple rows from worksheets.
Step 1: Open any existing Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: enter some sample data in row 1 to 5
Step 6: Now press F5 to execute the code
Explained VBA Code to Delete Multiple Rows:
‘Starting program and sub procedure to write VBA code to delete entire row from sheet
Sub sbVBS_To_Delete_Multiple_Rows_C()
Rows(“1:3”).EntireRow.Delete
End Sub
how to delete (suppose 1-10 rows) in multiple CSV simultaneously (for folder option)
Hello Yogesh,
We can do this, you can open the csv files in Excel using VBA and then delete the rows using Rows(“1:10”).Delete method.
Steps:
-loop though the folder
– if you have multiple types of files, check if the file is in your desired format (.csv)
– open the csv file in Excel
-Delete the Rows 1 to 10
You can find the detailed examples in our 100+ useful macros list.
Hope this helps! Thanks-PNRao!
Hi there,
What about deleting random rows of data in a data set? For example, if I have say rows 2,4,5,8,12 to delete (delete line of code included in a macro), there is always one left behind and I need to delete this separately. The random rows are selected using a criteria in the macro.
Thanks in advance.
Hi Leela,
You can store the random rows in an array or worksheet and delete by looping through these items.
Thanks-PNRao!
i download a report in notepad, copy it to excel and delete extra columns and headers. i am in process of automating the excel report. how to delete random rows for example , headers like “—–“, “Program” etc., kindly help me
Note:, if done in loop then it is wrong to do it wiht “arrDel=Array(2,4,5,8,12)” — not in ascending order
Do it in descending order: arrDel=Array(12,8,5,4,2)
It is important, because: after you delete row 2, what was on row 4 is now on row 3, etc.
So dong it with arrDel=Array(2,4,5,8,12) you will be deleting the original rows 2,5,7,11,15, this is because:
-After deleting 2, the rest that had higher number are in one less, so the row number 5 becomes the number 4, and so on
-The after deleting what now is row number 4 (old row number five), all rows with number higher than 4 will be in one row less, so original 8 will be on row number 6 (two rows have been deleted, so substract 2)
-And so on
If done in descending order, deleting the highest row number will not affect the rest of rows number that want to be deleted, since they have a lower row number than the deleted one.
Always have in mind: deleting one row, moves all the higher rows one number done.
Thanks for pointing out the scenario.
How can a selection of rows on the worksheet be deleted. Can we use
“Activesheets(“Sheet1″).rows(12,8,5,4,2).select
Selection.rows.entirerow.delete ”
as a valid command.
Sir, pl explain the function of Ubound() in the below sub. is it taking the largest no in the array and then reducing by 1? Thanks
arrDel=Array(12,8,5,4,2)
For i=0 to UBound(arrDel,1)
Rows(arrDel(i)).Delete
Next
Last one. How to code if we have to select rows on the worksheet and want those to be deleted using vba function. pl help thanks.
Hi, what if you want to delete a set of rows satisfying some criteria for example, rows that have “calendar”text in the first column?
what if I want to delete every 55th to 61st rows starting with the active cell?
how about deleting a range of rows like below but that number changes everyday? & the date changes to previous working day?
Sub Delete_Rows()
‘
‘ Delete_Rows Macro
‘
‘
Selection.AutoFilter
ActiveSheet.Range(“$A$1:$F$1403”).AutoFilter Field:=1, Criteria1:=Array( _
“BRANCH 301111 29JN18”, _
“BRANCH 301111 PORTLAND (301111)”, _
“TEL CB OVRD CAL-DT TR-CODE ORIG TIME”, “=”), Operator:= _
xlFilterValues
Rows(“39:1362”).Select
Selection.Delete Shift:=xlUp
End Sub
Sub DelRows()
‘
‘ DelRows Macro
‘ delete specified rows
‘
‘ Keyboard Shortcut: Ctrl+d
‘
Dim fromrow As String
Dim torow As String
On Error Resume Next ‘Blanket error handling
‘Display inputbox to prompt user for row/column
fromrow = InputBox(“Type a row number from where to delete”)
torow = InputBox(“Type a row number upto where to delete”)
Rows(“fromrow:torow”).EntireRow.Delete
End Sub
Sub DelRows()
‘
‘ DelRows Macro
‘ delete specified rows
‘
‘ Keyboard Shortcut: Ctrl+d
‘
Dim fromrow As String
Dim torow As String
On Error Resume Next ‘Blanket error handling
‘Display inputbox to prompt user for row/column
fromrow = InputBox(“Type a row number from where to delete”)
torow = InputBox(“Type a row number upto where to delete”)
Rows(“fromrow#:torow#”).EntireRow.Delete
End Sub
i made this function on marco but doesnt working
Hi
I have a VBA code that sorts and filters data from one excel table and save 48 different reports on my desktop. but based on those filters, some generated reports have only 1 row (headers) and no data. How can I add some VBA code to my file that prevents to save files that has just one row (header) and no data?
Thank you