Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete a row in Excel VBA

Tags:

excel

vba

I have this piece of code which finds the excel row of an item from a list and deletes the items from a list. What I want... is to delete the Excel row as well.

The code is here

Private Sub imperecheaza_Click() Dim ws As Worksheet Dim Rand As Long Set ws = Worksheets("BD_IR")     Rand = 3 Do While ws.Cells(Rand, 4).Value <> "" And Rand < 65000    If ws.Cells(Rand, 4).Value = gksluri.Value * 1 And ws.Cells(Rand, 5).Value = gksluri.List(gksluri.ListIndex, 1) * 1 Then             ws.Range(Rand, 1).EntireRow.Delete '(here I want to delete the entire row that meets the criteria from the If statement)             gksluri.RemoveItem gksluri.ListIndex             Exit Do     End If Rand = Rand + 1 Loop End Sub 

Where I added ws.Range(Rand,1).EntireRow.Delete is where I want to delete the entire row but I don't know how to do it. What I want... if it finds the same value in a cell like in some selected item of my list to be able to remove both the entire row in excel and the item from the listbox. It works to remove the item from the listbox but I don't know how to remove the row as well

like image 442
Andrei Ion Avatar asked Oct 21 '11 15:10

Andrei Ion


People also ask

How do you delete a row in excel VBA?

To delete an entire row in Excel using VBA, you need to use the EntireRow. Delete method. The above code first specifies the row that needs to be deleted (which is done by specifying the number in bracket) and then uses the EntireRow. Delete method to delete it.

How do I delete a row based on a cell value in excel VBA?

Using a Macro to Delete Rows Based on Cell Values The overall process is two simple steps: The first step is to filter the rows based on filter criteria for the values to be deleted. Then the macro deletes the visible cells in the range.

How do you delete row and move up in excel VBA?

VBA to Delete Range in Excel – Syntax Here, Shift will be xlToLeft or xlUp. Shift:=xlLeft will shifts the cells towards Left side after deletion of the range. And Shift:=xlUp will shifts the cells towards Upper side after deletion of the range.


1 Answers

Chris Nielsen's solution is simple and will work well. A slightly shorter option would be...

ws.Rows(Rand).Delete 

...note there is no need to specify a Shift when deleting a row as, by definition, it's not possible to shift left

Incidentally, my preferred method for deleting rows is to use...

ws.Rows(Rand) = "" 

...in the initial loop. I then use a Sort function to push these rows to the bottom of the data. The main reason for this is because deleting single rows can be a very slow procedure (if you are deleting >100). It also ensures nothing gets missed as per Robert Ilbrink's comment

You can learn the code for sorting by recording a macro and reducing the code as demonstrated in this expert Excel video. I have a suspicion that the neatest method (Range("A1:Z10").Sort Key1:=Range("A1"), Order1:=xlSortAscending/Descending, Header:=xlYes/No) can only be discovered on pre-2007 versions of Excel...but you can always reduce the 2007/2010 equivalent code

Couple more points...if your list is not already sorted by a column and you wish to retain the order, you can stick the row number 'Rand' in a spare column to the right of each row as you loop through. You would then sort by that comment and eliminate it

If your data rows contain formatting, you may wish to find the end of the new data range and delete the rows that you cleared earlier. That's to keep the file size down. Note that a single large delete at the end of the procedure will not impair your code's performance in the same way that deleting single rows does

like image 200
Ed Bolton Avatar answered Oct 25 '22 15:10

Ed Bolton