+ Reply to Thread
Results 1 to 11 of 11

Having trouble with VBA macro that deletes autofiltered rows

  1. #1
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Having trouble with VBA macro that deletes autofiltered rows

    Hi,
    I'm a newbie to VBA, and am trying to delete patients from a spreadsheet after they have been discharged. What happens is that the macro deletes the top line of an current patient, even if the value in cell AJ3 is 1 (i.e. patient is still in the center). Could you tell me what I'm doing wrong?
    Thank you!
    Barry


    Please Login or Register  to view this content.
    Last edited by BChester; 06-15-2016 at 01:25 PM. Reason: Didnt comply with rule#3

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Thanks Norie, sorry for the error.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Barry

    No problem, the code tags make it easier to read/copy the code.

    As to the issue, is the data being filtered as you want?

    Also, do you have any merged cells?

  5. #5
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Having trouble with VBA macro that deletes autofiltered rows

    The filter does seem be OK. I have another macro to show only discharged patients, and it works.

    However, there are merged cells in the table which are affected by the autofilter (i.e. patient name is 5 merged cells, and there are others). Each of the lines related to the patient name turn to 0 once the patient has discharged.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Any chance you could upload a sample workbook, obviously without any sensitive data?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  7. #7
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Hoping the upload is attached. Password BEC.
    Thanks!
    Attached Files Attached Files

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Having trouble with VBA macro that deletes autofiltered rows

    I'm pretty sure the problem is the merged cells.

    What exactly are you trying to delete?

  9. #9
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Having trouble with VBA macro that deletes autofiltered rows

    I am trying to delete one patient at a time.

    If you change the estimated discharge date (column D) to anything before today (i.e. 6/14) column AJ values change to 0.

    Filtering by clicking on "Show past pts" shows only patients whose discharge date is before today. I was hoping to use the same filter to be able to delete visible cells after filtering AJ for "0", but it always seems to delete the top row (row 3), in addition to deleting discharged patients.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Having trouble with VBA macro that deletes autofiltered rows

    Try this.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-14-2016
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    6

    Re: Having trouble with VBA macro that deletes autofiltered rows

    You are a magician!
    Thank you so much, works perfectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro Deletes all rows except....
    By TheDriver85 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-25-2012, 06:37 PM
  2. [SOLVED] Macro which deletes rows
    By kjshep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2012, 01:09 PM
  3. Macro that deletes rows below a certain value
    By Biased Historian in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-15-2011, 09:45 PM
  4. Macro which deletes rows stops at empty row
    By john_london in forum Excel General
    Replies: 17
    Last Post: 09-28-2010, 06:00 PM
  5. Macro for deleting autofiltered rows
    By tuph in forum Excel General
    Replies: 3
    Last Post: 12-17-2006, 10:30 PM
  6. Macro that Deletes All rows except first row
    By curtney in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-18-2006, 01:22 PM
  7. [SOLVED] Macro that deletes certain rows only
    By supamari0 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-09-2006, 11:15 AM
  8. [SOLVED] Macro that deletes certain rows and not others
    By Roger in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-02-2005, 07:06 PM

Tags for this Thread

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