Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Excel Macro delete lines with a blank address cell (office 2006 and 2007)

    I have a csv file that is a "data dump" from my medical package of patient names and addresses I use to create a mailing list.
    In excel old and new version (will be migrating but still like the old version better) is there a way to create a macro that will delete and records that have the address1 field (just the first address1 field, not addess2 field) blank so that I don't have to manually sort and delete patients who are lost to follow up??
    Thanks I have attached it as an xls file but in reality i am using it as a csv file but the lounge won't let me upload it as a csv file.
    Jerome
    Attached Files Attached Files
    • File Type: xls c.xls (13.5 KB, 2 views)

  2. #2
    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: Excel Macro delete lines with a blank address cell (office 2006 and 20

    No need for a macro or doing it manually. Just add an autofilter, filter the column on Blanks and then delete the visible rows...

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Macro delete lines with a blank address cell (office 2006 and 20

    Yes, but I use a web based program to import the csv file of addresses and I don't think that the autofilter will hide or delete the blank field rows to the import in the web program. So I need to do a macro that will take it one step further and get rid of the records that have blank address
    Jerome

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

    Re: Excel Macro delete lines with a blank address cell (office 2006 and 20

    You can use this macro:

    Sub DeleteBlankAddresses()
    Dim n As Long
    Dim r As Long
    Application.ScreenUpdating = False
    n = Cells(Rows.Count, 4).End(xlUp).Row
    For r = n To 2 Step -1
    If Trim(Cells(r, 4)) = "" Then
    Cells(r, 4).EntireRow.Delete
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub

    It should work in any version of Excel.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Macro delete lines with a blank address cell (office 2006 and 20

    Hi Hans, beyond perfect thanks
    Jerome

Posting Permissions

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