pushfere.blogg.se - Delete empty rows in excel 2010

Now you can observe that the rows are deleted from worksheet if the cell is blank.Įxplained VBA Code to Delete Rows if Cell is Empty/Blanks Step 5: Enter some sample data in first column from row 1 to 20 and leave some blank cells to check Step 4: Copy the above code and paste in the code module which have inserted in the above step Step 3: Insert a code module from then insert menu

Step 2: Press Alt+F11 – This will open the VBA Editor Please follow the below steps to execute the VBA code to delete rows if Cell is Empty/Blanks from Excel worksheets. Instructions to run the VBA code to delete rows if Cell is Empty/Blanks This code will delete the rows (1 to 20) if Cell is Empty/Blanks. The following VBA code is to delete rows based if Cell is Empty/Blanks from the excel worksheet. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.ĭelete rows if Cell is Empty/Blanks using VBA: Examples And Row Numbers are the row numbers to delete. Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Here trim( ) =””is to check if the cell is empty. I haven't tested both the examples for such huge rows :) Do let me know if you get any errors. You can also use Autofilter to delete the rows. If Not delrange Is Nothing Then delrange.Delete If Len(Trim(.Range("A" & i).Value)) = 0 Then With Sheets("Sheet1") ' Get the last Row in Col A Range("A" & j, "A" & j - 7999).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Range("A2:A" & j).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Is there any faster alternative to this? Private Sub Del_rows()ĭim i As Long, j As Long, arrShts As VariantĪrrShts = VBA.Array("Sheet1") 'add additional sheets as requiredįor j =.

This code is super slow on some older machines and takes about 40 minutes to finish (but does the job). I could not use the Special-> Blanks -> Delete Sheet Rows feature on 2010 because 2007 has an upper bound of approximately 8000 non contiguous rows. I have the following piece of code that takes in the empty rows from Column A and then deletes the entire row.