Deleteing Rows in excel

Cindy - Nov 15, 2010 at 09:29 AM
 Cindy - Nov 16, 2010 at 10:10 PM

I work with a lot of files every day and the number of data in column A is different from the rest of the column. The number of data are usually less in column A then the some of the columns. Is there a way to run the macro to delete all rows after the last data of column A? Lets say that the last data in column A is on A141, then I want the macro to delete all rows after A141. The number of data varies in column A, therefore I can not use a set range, but need to go off the last data in column A.

Need your help. Thanks!

2 replies

data_encoder Posts 13 Registration date Wednesday November 3, 2010 Status Member Last seen November 15, 2010 1
Nov 15, 2010 at 01:08 PM
I have a macro that deletes empty columns, and you can designate column A. If you have headers that are blank, I suggest to add some header info just so it doesnt get deleted, eg., if column A starts at row 3 with header info, make sure row 1 and 2 has some info or this gets deleted.

Give this a shot:

Sub DeleteEmptyColumns() 

Dim col As String * 1 

col = InputBox(Prompt:="Enter Column: eg. A (for the 1st column)", _ 
          Title:="Enter Column Letter To Delete Blanks", Default:="A") 
If col = "Enter Column" Or _ 
   col = vbNullString Then 
         Exit Sub 
    'Deletes all rows with empty column col 
     Columns(col & ":" & col).SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
End If 

End Sub 

Awesome!!! Thanks!