+ Reply to Thread
Results 1 to 14 of 14

deleting rows with a cetain cell valu

  1. #1
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Arrow deleting rows with a cetain cell valu

    Hi

    Been trying to write this macro for my work but not getting very far: upload a load od data to excel what I want to do is run a macro that will on leave the rows where in a particular cell there is a certain numerical value ie between 0 - 3

    all help is appreciated

    todd.p2

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: deleting rows with a cetain cell valu

    Record a macro: switch Autofilter on if it isn't already; Filter on the column for the value; select the visible rows; delete them using the delete icon on the ribbon.

    Come back with the code and a sample workbook to get it refined and generalised.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    Hi toddp2,

    Here's a non-loop way (just initially try it on a copy of your data as the results cannot be undone if they're not as expected):

    Please Login or Register  to view this content.
    Just change the two constant variables at the start of the macro to suit your needs (if necessary).

    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  4. #4
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Re: deleting rows with a cetain cell valu

    Hi Robert

    works perfectly, couple of questions as I'm no expert
    I wanted to put in ctlr p to run the macro but everytime I do this it goes to the print page I've done this before but don't know where I'm going wrong.
    and secondly what do I need to do to make this work on any pc its for work.

    Thanks for all your help
    Toddp (Peter)

  5. #5
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    I wanted to put in ctlr p to run the macro but everytime I do this it goes to the print page I've done this before but don't know where I'm going wrong.
    To assign a shortcut to a macro, follow these five steps:

    1. If it isn't already open the workbook with the macro already installed
    2. Press Alt + F8 (that is press and hold the Alt keyboard button followed the F8 button)
    3. Click on the relevant macro name and then click Options
    4. Enter the letter you wish to use as a shortcut for the macro. I would avoid standard shortcuts like Ctrl + p as some users use these on a regular basis.
    5. Click OK and then then close the Macro dialog

    secondly what do I need to do to make this work on any pc its for work
    The only ways I could of to make this work would be to have the macro on a standard workbook (perhaps on SharePoint??) that all users download and use or to load the macro on each user's Personal workbook (Google "Excel VBA personal workbook" for plenty of references about this).

    HTH

    Robert

  6. #6
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Re: deleting rows with a cetain cell valu

    Hi Robert

    The fact that this works perfect where would I add this formatting to the macro

    Please Login or Register  to view this content.
    it just tidies the final presentation up

    thanks
    Peter
    Last edited by jeffreybrown; 01-26-2017 at 08:40 AM. Reason: Please use code tags when posting code

  7. #7
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    If you want your code to run after mine you can do it this way:

    Please Login or Register  to view this content.
    Please make sure you use the correct tags when posting code as I have done.

    Thanks,

    Robert

  8. #8
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Re: deleting rows with a cetain cell valu

    Hi

    Don't know where I've went wrong the columns I wanted to delete were A,C,F,I & J and then format what was left, at the moment all disappears when I run the macro.

    Peter

  9. #9
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    Don't know where I've went wrong the columns I wanted to delete were A,C,F,I & J and then format what was left, at the moment all disappears when I run the macro.
    Neither do I as you said your macro works fine after mine was run

    I have incorporated how I would delete the desired columns and set the column widths for all columns to my original macro with the following:

    Please Login or Register  to view this content.
    I can't really offer any more advice I'm afraid

    Robert

  10. #10
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Re: deleting rows with a cetain cell valu

    Hi Robert

    Your help is invaluable the deleting of the columns works as in the fact it deletes the columns I want but what it also does is delete all the data in the remaining columns the data that I need

    Peter

  11. #11
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    I can't really offer any more advice I'm afraid
    Like I said I can't offer any other solutions as it works fine for me Note running the macro multiple times will delete columns you want to keep (i.e. running it once will delete column A and make column B the first column. Running the macro again will then delete column A (previously column B)) is that the issue?

    As your original post is solved it is best to start a new thread (with a link back to this thread if you think it will help) for your new query.

    Posting your workbook (devoid of any sensitive data) will also help anyone trying to provide a solution.

    Good luck with it.

    Robert
    Last edited by Trebor76; 01-27-2017 at 11:57 PM.

  12. #12
    Forum Contributor
    Join Date
    12-18-2015
    Location
    scotland
    MS-Off Ver
    MS office 365 business
    Posts
    186

    Re: deleting rows with a cetain cell valu

    Hi Robert

    Yes that's what's happening

    Peter

  13. #13
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    Yes that's what's happening
    So you need to build in some logic that doesn't just delete the columns each time the code is run.

  14. #14
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: deleting rows with a cetain cell valu

    Yes that's what's happening
    So you need to build in some logic that doesn't just delete the columns each time the code is run.

+ 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. Replies: 1
    Last Post: 09-21-2014, 07:34 PM
  2. Play code if cell a1's valu is greater than 0(zero)
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2014, 08:54 AM
  3. How to increase a cell valu by 1 with a press of a keyboard button?
    By aalfouzan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-15-2012, 05:42 AM
  4. Replies: 9
    Last Post: 02-26-2012, 07:20 AM
  5. Macro for selecting cetain cell and entering data into another
    By jlozolla in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2009, 08:29 AM
  6. [SOLVED] can conditional formatting on one cell value to another cell valu
    By Ring eye in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2006, 03:35 PM
  7. [SOLVED] I want to chage a whole rows text color based on single cell valu.
    By thediamondfam in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-14-2005, 11:51 AM

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