Delete a row in Excel VBA

Question

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  lt  gt     And Rand  lt  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

User · Answer

Better yet  use union to grab all the rows you want to delete  then delete them all at once  The rows need not be continuous  dim rng as range dim rDel as range  for each rng in  the range you re searching     if  Conditions to be met    true then       if not rDel is nothing then          set rDel   union rng rDel        else          set rDel   rng       end if    end if  next    rDel entirerow delete  That way you don t have to worry about sorting or things being at the bottom

User · Answer

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

User · Answer

Something like this will do it   Rows  12 12   Select Selection Delete   So in your code it would look like something like this   Rows CStr rand   amp       amp  CStr rand   Select Selection Delete

User · Answer

Change your line  ws Range Rand  1  EntireRow Delete   to   ws Cells Rand  1  EntireRow Delete

[excel] Delete a row in Excel VBA

Examples related to excel

Examples related to vba