Eliminate empty columns from right to left

[glennpc]

I have a range (with 5 rows of data (and a top row for column headers). Column A is just a person's first name, and the remaining columns are labeled A through F. The cells in the grid will have a simple YES or NO, but not every cell is filled in for each person-- at some point, the cells will all start to all be blank. That point is different for different people.

I have code that builds this range as a table, but what I want to do with VBA is to start from the right (in Col F) and assess if none of the 5 people have any YES or NO entries listed in this column, HIDE the column and then check the next column to the left which would be column E. This would continue until it doesn't have a column to delete (because in the column being checked, at least one of the cells has a YES or NO value.)

In the file I've attached, the code would HIDE the E and F columns, but then it would END SUB since we do have a non-blank value in column D.

I've been unable to figure out the VBA for this, and you can't really do it with the recorder. It has to work with a different set of YES and NO values in the grid, since that will vary from day to day. Anybody have an idea?

My simple worksheet is with my Excel table attached if you need to see it.

 

[vletm]

Hmm ... 'if none of the 5 people have any YES or NO entries listed in this column, HIDE' ... Okay ... 'This would continue until it doesn't have a column to delete' ... now delete? ... 'the code would HIDE the E and F columns' ... no!
Next version makes the 1st option.
Could You give one more time Your result?
Hide or Delete, and which columns?

 

[glennpc]

Sorry-- it would not DELETE columns, only HIDE them. The columns to be hidden would be the ones that are tested beginning with the far right column and that prove to have no data in the cells in the grid.

Your macro is PERFECT! It does exactly what I was looking for. You saved me a lot of research and effort. Thank you very much!

 

[glennpc]

This macro works great for this specific worksheet. However, I have two questions that I need to figure out if I'm going to be able to apply your code to another sheet. On this other sheet, I need to run the macro each day, and each day the number of rows is going to vary. I think I can run some code I already have to identify the last row of my data (put it in a variable called lrow) and then use lrow instead of the 8 you have in your first For statement (in this sheet, 7 would be the first row-- the one with the headers). So my first For statement would be For x= lrow to 7. My other For statement would be For y = 8 to lrow.

The other question would be related to the YES and NO values we test for. In this case those cells could have any text at all. Would it work to say
IF chk_yx IS NOT NULL then anybody = True ?

 

[glennpc]

I coded this up (used a different expression to ask if chk_yx isn't empty), but it does nothing. Do I have my variables mixed up? Here's my code:

Sub HideEmptyColumns()
  Application.ScreenUpdating = False
 
  Dim lRow As Long
  lRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
 
  a_tab = ActiveSheet.Name
  For x = 49 To 11 Step -1
  anybody = False
  For y = 8 To lRow
  chk_yx = Worksheets(a_tab).Cells(y, x)
  If Not IsNull(chk_yx) Then anybody = True
  Next y
  If Not anybody Then Worksheets(a_tab).Cells(1, x).ColumnWidth = 0
  Next x
 
  Application.ScreenUpdating = True
 
End Sub

 

[vletm]

The 1st y ... You have changed from 4 to 8? and x ... maybe same reason?
Anyway, in this sample, it is y stars from 4 and x end to 3.
... and IsNull is maybe better to use somewhere different case.
How do You 'UnHide' columns?
Check this version..

 

[glennpc]

I'm uploading my file. I'm focusing on the Labels columns-- Labels-1 through Labels-39. My actual table is C7:AW15 (columns A and B are for something else). Today, we have only 7 rows of data in the table (it could be different tomorrow)-- that's why I'm using lrow variable. The macro (Pink button labeled HIDE COLS) is HideEmptyColumns. When the macro is run with the data we have now, it should HIDE columns P through AW (in my table, this would be Labels-6 through Labels-39). Instead its doing nothing. Not sure why.

 

[vletm]

'Your file' was much much older than my version. There is only my old macro and so on ...
It's a little challenge to try to find out ...