Copying table but removing empty cells without deleting rows

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have what I thought was an easy thing to do, but struggling.

I have a table of many rows and columns. Within this table are many individual words spread out, but also lots of blank cells the content of which is dynamic and will be changing, something like this:

applesausage
berriespastahot dogs
cheesecerealtoast
avocadosandwichchips
beefbrioche

I'm trying to create a new table below it which removes all the empty cells, and ideally puts each column into alphabetical order, something like this

appleavocadocerealbriochepastachips
beefberriessausagetoastsandwichhot dogs
cheese

My range is AD62:CI89 and this is fixed, so there's only ever going to be 28 rows, and there will be a maximum of 8 articles in the end for each column. There's no relation between each column, so loosing the empty cells won't bother me. The important thing is to keep the columns separate.

I'd assumed I could used filters to remove empty cells, but I can only work out how to filter on one column, and that hides the information from the other cells too!

HELP!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
If you weren't sorting the data then it would be easy to do it manually but as you are then try running one of the macro's below, assumes that your data starts in A2 and that your blank cells are truly blank.

VBA Code:
Sub flatterno()
    Dim lr As Long, lc As Long, i As Long

    Application.ScreenUpdating = False

    lr = ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    For i = 1 To lc
        Range(Cells(2, i), Cells(lr, i)).Sort Cells(2, i), 1, , , , , , 2
    Next

    Application.ScreenUpdating = True
End Sub

VBA Code:
Sub flatterno2()
    Dim lr As Long, lc As Long, i As Long

    Application.ScreenUpdating = False

    lr = ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    On Error Resume Next
    Range("A2", Cells(lr, lc)).SpecialCells(4).Delete
    On Error GoTo 0

    For i = 1 To lc
        Range(Cells(2, i), Cells(lr, i)).Sort Cells(2, i), 1, , , , , , 2
    Next

    Application.ScreenUpdating = True
End Sub
 
Upvote 0
That's amazing thank you

Out of interest, would you be able to explain how to do it without sorting the data, if that's easier maybe that's the way ahead, besides which,I would like to learn how to do it

Many thanks
 
Upvote 0
explain how to do it without sorting the data

Select the Range
Press Ctrl + G
Click Special
Check the Blanks checkbox
Click OK
Right Click
Click Delete
Shift cells up
Click OK
 
Upvote 0
that's super helpful thank you. This does the action but unfortunately as the data is dynamic I was hoping for a solution that updates when any of the cells in the range is also updated. I've learnt so much in a few days on Excel, but this evades me!
 
Upvote 0
This does the action but unfortunately as the data is dynamic I was hoping for a solution that updates when any of the cells in the range is also updated.
Wasn't what you asked for in the original post, anyway....

How is the data updated?
What is the maximum amount of columns and rows the data can be in?

I am going to be out for most of the day. I'll look at the post when I get back.
 
Upvote 0
sorry I thought I'd made that clear by saying 'dynamic and will be updating' - forgive me if this isn't the right phrase - as I said, learning here, and I do really appreciate your words of help.

I have one table within a sheet which lists people down the row headings, and types of food in the columns headings. This then reports TRUE or FALSE deepening on a few other tables on different sheets.

Beneath this I have a copy of the above table but this time there's a formula which replaces the word TRUE with any food item, and removes any FALSE

The next stage I wanted was then to then remove the blanks (and then maybe alpha then).

To give you some context, I am trying to create list boxes of the food that each person will eat, but want it to update if a person adds a food item. My range is AD62:CI89 and this is fixed, so there's only ever going to be 28 rows, and there will be a maximum of 8 food articles in the end for each column. There will never be any new foods or new people.

I really do appreciate it
 
Upvote 0
The next stage I wanted was then to then remove the blanks (and then maybe alpha then).
If there are formulas in the cells then you haven't got any blanks, at best you have empty strings.

If the cells are updated using a formula then your only option is a Worksheet_Calculate event.
From what you posted I think all you need to do is a sort.
The code below goes in the sheet module of the sheet being sorted (Right click the sheet tab and click view code).

Test it in a copy of your workbook, if it doesn't work then we might have to look at removing the formulas from the "blank" cells.

VBA Code:
Private Sub Worksheet_Calculate()
    Dim i As Long

    Application.ScreenUpdating = False
   
    For i = 30 To 87
        Range(Cells(62, i), Cells(89, i)).Sort Cells(2, i), 1, , , , , , 2
    Next

    Application.ScreenUpdating = True

End Sub
 
Last edited:
Upvote 0
Thanks, I really do appreciate your help. I'm trying to use my time before a big work project starts to put myself and getting a bit more under the hood of excel is one thing I'm working.

Your help has made a difference
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top