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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,061
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

flatterno

New Member
Joined
Mar 24, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,080
Messages
5,639,947
Members
417,120
Latest member
Pavithra devi

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
Top