+ Reply to Thread
Results 1 to 7 of 7

Deleting rows based on condition

  1. #1
    Forum Contributor
    Join Date
    01-18-2005
    Posts
    238

    Deleting rows based on condition

    I am trying to delete all rows that don't have any data in column f

    Row 1 is column headings

    Column F is a date field and is not sorted


    I was thinking of doing the following (but don't know the code), but maybe there is an easier way.

    I was going to sort from A to F from 2 to last row

    then find the first empty row after the sort

    then delete all the rows below the last data row



    Thanks for any help or direction

    Josh

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,740
    Hi,

    Why not just autofilter on column F for blanks, then select all the filtered rows and delete them. Then switch off the autofilter.

    HTH

  3. #3
    Forum Contributor
    Join Date
    01-18-2005
    Posts
    238

    thanks Richard

    Sorry for the delay, have been out of town. That solution will work.



    POST HAS BEEN SOLVED.

    Thanks

  4. #4
    Forum Contributor
    Join Date
    01-18-2005
    Posts
    238

    Had 1 more question

    I did the auto filter through the macro recorder and it worked. How do I use code to select all the filtered rows (where the first blank cell in column f) occurs.


    thanks


    Josh

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,740
    Hi,

    In these circumstances I generally use the .SpecialCells(xlCellTypeVisible).EntireRow.Delete instruction, being careful to avoid the column header row.

    HTH

  6. #6
    Forum Contributor
    Join Date
    01-18-2005
    Posts
    238

    Filter not working

    I have the filter on in my sheet and below is my code so far.

    For some reason that I cannot figure out, it is not sorting the column F to descending, it doesn't look like it's doing anything.



    HTML Code: 
     Dim LR As Integer
        Dim n As Integer
        
        LR = shData.Range("A65536").End(xlUp).Row
    
        shData.Activate
        
        shData.Range("A1:M" & LR).Select
        
        Range("A1:M" & LR).Sort Key1:=Range("M1"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal

    I want the data to sort descending and then I will set n to the last row with data in column F, so that way I can set the row source of the listbox from n to LR and that should be all the empty fields in column f


    thanks for any help

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,740
    Hi Josh,

    This thread seems to have changed its title and in any case the request does not match the title. It's now about sorting rather than filtering or deleting rows as it was earlier.

    Would you correct this please and attempt to stick to one subject matter per thread. I have not given an infraction.

    Incidentally the clue to your question is actually in your code!

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1