Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete rows (Excel 2000)

    I have reviewed the threads on 'Delete Rows' and they don't solve my problem. I have a list of Data in Columns A - Q that varies in the number of rows each month. I need a statement that will delete the row if Cell A is blank. If Cell A is occupied, keep the row. This needs to go down to A65536. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete rows (Excel 2000)

    You can use a macro like this:

    Sub DeleteRows()
    Dim lngRow As Long
    Dim lngMaxRow As Long

    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = lngMaxRow To 1 Step -1
    If Range("A" & lngRow) = "" Then
    Range("A" & lngRow).EntireRow.Delete
    End If
    Next lngRow
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows (Excel 2000)

    If column A can have empty cells, then your code will miss deleting any rows at the end if column A is empty. I think you need to find out if any of the columns are guranteed to always have a value and use that column. Cindy, are any of those columns guranteed to always have a value?
    Legare Coleman

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Delete rows (Excel 2000)

    How about something simple like:

    <pre>Sub DeleteBlankA()
    Range("A1:A65536").SpecialCells(xlCellTypeBlanks). EntireRow.Delete
    End Sub</pre>


    This should be much faster than looping...

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows (Excel 2000)

    Very Nice!!!! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete rows (Excel 2000)

    <big>Excel</big>lent!

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete rows (Excel 2000)

    Awesome!!!!!!!!!!! Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •