+ Reply to Thread
Results 1 to 6 of 6

Expand multiple special cell selection by one cell

  1. #1
    Registered User
    Join Date
    05-21-2019
    Location
    Wisconsin
    MS-Off Ver
    14
    Posts
    4

    Question Expand multiple special cell selection by one cell

    I have an Excel files that has blanks that I need to remove. I can use Select > Special and highlight all of my banks to delete them and shift data without issue. My problem is the data in the cells are associated in pairs of twos so if I have a blank and have it highlighted I need to expand my selection on to the left to make sure I get the cell the describes it too. When I use the special feature to highlight all the random blank cell if is shift + arrow left it only expands one of the selected cells, not all of them.

    Any suggestions or a different way I could go around this? You can see in this sample if I deleted all the blank cells it would shift the 100, 200, 300 descriptions around so I need to expand those selections one to the left to delete the description as well. If I CTRL+ ARROW LEFT the active selected cell in the top right is the only one that expands the selection.

    EXCapture.PNG


    Thanks,

    Mike

  2. #2
    Forum Moderator - RIP 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
    29,464

    Re: Expand multiple special cell selection by one cell

    Hi and welcome to the forum

    Please upload a workbook. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Expand multiple special cell selection by one cell

    You don't only want to select the cell to the left. In your example you have one cell among the 200 column and 2 among the 300 column which are blank, and in these cases you would want to select the cell(s) on the right to be deleted.

    Pete

  4. #4
    Registered User
    Join Date
    05-21-2019
    Location
    Wisconsin
    MS-Off Ver
    14
    Posts
    4

    Re: Expand multiple special cell selection by one cell

    Quote Originally Posted by Pete_UK View Post
    You don't only want to select the cell to the left. In your example you have one cell among the 200 column and 2 among the 300 column which are blank, and in these cases you would want to select the cell(s) on the right to be deleted.

    Pete
    You're right. I totally botched my example. Only the values should have been blank, not that 100, 200, 300 descriptions.

  5. #5
    Registered User
    Join Date
    05-21-2019
    Location
    Wisconsin
    MS-Off Ver
    14
    Posts
    4

    Re: Expand multiple special cell selection by one cell

    This data is being exported from Access via SQL statement and then will be imported into another index (ISAM?) once it is properly formatted.

    When exported from Access it will look like this:

    Attachment 703164


    I need to have the category code in the row instead of the column, right night to the price. All zeros need to be removed so here I can use the special select to highlight all of the white space, delete and shift left; however I need to get the category to the left of the price selected so I can delete it.

    Attachment 703165


    Hoping to have a final result like this, note the 500 that moved it's way into the first position after the 100 and 250 that were blank had been removed.

    Attachment 703166

    Access export query attached. Thank you!
    Last edited by MikeOrlando02; 11-13-2020 at 02:37 PM.

  6. #6
    Registered User
    Join Date
    05-21-2019
    Location
    Wisconsin
    MS-Off Ver
    14
    Posts
    4

    Re: Expand multiple special cell selection by one cell

    The workaround that I came up with is to temporarily append the category number, using a filter for non-empty cells only, to the start of the cell with a delimiter. Then I can delete the category column, find and delete my blanks, and then split my cell b y the delimiter to restore my category column and keep it associated with the cell data. It is a lot of steps, but this is really a one time operation. Best I can come up with unless there is better advise.

+ 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. [SOLVED] How can I expand selection of multiple non contiguous cells by certain number of columns?
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2019, 01:20 PM
  2. VBA code to expand selection based on value in next cell and then merge-and-center
    By amarkham in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2018, 06:15 PM
  3. Replies: 5
    Last Post: 07-06-2014, 11:41 PM
  4. Replies: 3
    Last Post: 09-20-2012, 03:11 PM
  5. [SOLVED] Expand selection to multiple cells
    By bobbybill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2012, 10:53 AM
  6. Auto expand range based on first cell selection
    By Jimmy Moggles in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2011, 07:56 AM
  7. [SOLVED] Special Cell Selection
    By bodhisatvaofboogie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2006, 08:15 AM

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