+ Reply to Thread
Results 1 to 11 of 11

Deleting specific rows in a column, within a larger row

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Commack, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Deleting specific rows in a column, within a larger row

    Test Workbook.xlsx

    I have a spreadsheet with columns A through O. Columns A through N have one piece of information in them. Column O contains multiple rows of cells with dates in them. I need to delete all dates before 1/1/2011 and after 6/30/2011. I tried using Delete Sheet Row; it seemed to work but then data further down in other columns was missing.

    Doing it automatically would be nice as the real spreadsheet has about 600 cases, but I don't care if I have to do it one case at a time. Anyone have any ideas?
    Thanks!

  2. #2
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    Long Island towns...my old stomping grounds ;-)

    How are you deleting rows? By selecting a row using the far left column of row numbers? I tried that and it seemed to work OK. Maybe I'm missing something...

    edit: I also tried selecting dates and clicking "Delete | Delete Sheet Rows" and that seemed OK.
    Last edited by mike65535; 07-23-2013 at 10:58 AM.

  3. #3
    Registered User
    Join Date
    07-23-2013
    Location
    Commack, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Deleting specific rows in a column, within a larger row

    I was highlighting the cells themselves. I will try your way. Thanks!

  4. #4
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    OK... seems if I delete the "right" row in a specific group, apparently I can delete the common data.... I never liked merge and center...is that what you are seeing perhaps?

  5. #5
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    Yes, that's it - the common data is in the first row so if you delete that row...poof!

  6. #6
    Registered User
    Join Date
    07-23-2013
    Location
    Commack, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Deleting specific rows in a column, within a larger row

    I have to admit I have no idea what you are talking about (merge and center, common data). My non-profit has no money for training! So, it is usually the first couple of rows of dates that I need to keep - in the year 2011. Can I just use your first suggestion and delete by selecting rows from the left column?

  7. #7
    Registered User
    Join Date
    07-23-2013
    Location
    Commack, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Deleting specific rows in a column, within a larger row

    Nope, just tried it, and other data disappeared.

  8. #8
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    What I meant by common data is all the data to the left of the dates. It's common to a group of rows ( first name, last name, address, etc)

    "Merge and center" is a cosmetic tool to take the data that's actually only in one cell (say the Client ID - which is in cell A2 for your first group) and make it appear across multiple cells (your A2 thru A23)
    There's a "Merge & center" function in Excel that was used to do that in your xls.

    If you delete that row (row 2) by mistake (or on purpose) it removes the client ID, names addresses, etc which is your problem I think.

    For a good database/spreadsheet, that data should be in each row, but someone decided it looked better to merge-and-center, so now you have a problem.

  9. #9
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    If you can't figure out what to do let me know. It might be tedious but I can describe

    (I'd get rid of the pesky merge-center formatting - you can restore later - and go from there)

  10. #10
    Registered User
    Join Date
    07-23-2013
    Location
    Commack, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Deleting specific rows in a column, within a larger row

    I really appreciate your help. I have no idea what to do. I need to print this out, so I was trying to make it more concise and just display the needed info. But at this point I was going to just print it out with all the dates.

    If you have time to send me directions that would be great, otherwise I will just print the whole thing.
    Thanks again.

  11. #11
    Registered User
    Join Date
    07-19-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Deleting specific rows in a column, within a larger row

    Here's what I would do - I can be a little brute force, but this might be just what you need...

    First make a back-up copy of your sheet!

    Then remove the merge-and-center aspect. There are a few ways to do this - click the upper-left corner square of the spreadsheet (at the intersection of the horizontal and vertical headers) - this selects the entire spreadsheet. Then under "Alignment" at the top of the screen "Merge&Center" should be highlighted. Click it to turn it off.

    Copy and paste the data from the first row in each group to the rest of the rows in each group. Be sure Excel copies it without doing anything funny like auto-incrementing.

    Should look like the attached

    Test Workbook modified.xlsx

    Now delete any rows you want and you'll still retain the "common data".

    When you are done, you can clean up the common data if you want by using the Merge&Center.

+ 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. Finding specific text in a column and deleting rows
    By VKS in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2013, 04:09 PM
  2. Deleting rows if specific cell is blank or have specific text
    By JoaoFerreira1985 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2013, 01:18 PM
  3. Deleting rows with empty cells in a specific column
    By vzw1965 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-09-2011, 10:55 AM
  4. Deleting Rows with a specific value from a variable column (constant title)
    By seloni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2011, 04:08 AM
  5. Deleting rows with a specific value in the first column
    By nevrborn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2007, 06:44 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