select cell have that row selected and hide columns that are empty.

Adi64 · Feb 17th 2021

I have a worksheet with 600 rows and 500 columns. I would like to be able to select a cell have that row selected from column 1 to 500 then have the columns that have empty cells Hidden. I found this code below by Bryce. But it has a fixed to row 1. Thanks.

Code

Sub hideColumns()
Dim ws As Worksheet
Dim rng As Range
Application.ScreenUpdating = False
Set ws = Sheet1
For Each rng In ws.Range("A58:ADO58")
If rng.Value = "" Then
rng.EntireColumn.Hidden = True
End If
Next rng
End Sub

Display More

**alansidman** · Feb 18th 2021

So if any column has an empty cell, you wish to hide the entire column. Is that what you are asking for?

**alansidman** · Feb 18th 2021

Code

Option Explicit


Sub hidecol()
Dim rng As Range, c As Range
Dim col As Long, r As Long
Set rng = Range("A1").CurrentRegion
For Each c In rng
If c = "" Then
col = c.Column
r = c.Row
Cells(r, col).EntireColumn.Hidden = True
End If
Next c
MsgBox "complete"
End Sub

Display More

**royUK** · Feb 18th 2021

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Note: no apostrophe in the tags, just used for demonstration here.

['code]

your code goes between these tags

['/code]

Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Feb 18th 2021

Try this aternative code

Code

Option Explicit

Sub hideColumns()
    Dim ws As Worksheet
    Dim rRng As Range
    Dim iX As Integer
    Application.ScreenUpdating = False
    
    Set ws = Sheet1
    Set rRng = ws.Range("A58:ADO58")
    
    For iX = 1 To rRng.Columns.Count
        rRng.Columns(iX).EntireColumn.Hidden = Application.WorksheetFunction.CountBlank(rRng.Columns(iX)) > 0
    Next iX
End Sub

Display More

Adi64 · Feb 18th 2021

Alan, Roy,

Thank you both for taking the time to answer my question, I'll do better regarding the Code tags in future.

royUK your code works perfectly, the only issue is that the user is fixed using the range in line 10 ("A58:ADO58") I manually changed that line to one suitable for my sheet and it hid the columns that had empty cells.

I user needs to be able to select any row and not be fixed to ("A58:ADO58"). Is it possible to replace this ("A58:ADO58") with a manual entry, such click on a cell and use that row.

Thanks again

Adrian

**royUK** · Feb 18th 2021

Maybe Double_Click would be better, a user is less likely to double click a cell accidentally.

I'm not entirely sure what you mean, but try this

Adi64 · Mar 1st 2021

Hi, Roy sorry for the delay in answering, I have been off shift. I tried your code after populating some test data in the cell range H5:QF642. It hid every cell regardless of the cell being empty or not. Maybe I wasn't clear enough what I am looking for is, I double click (I like that feature) on row 30 Could be any row from 5 to 642 the result I am looking for is every cell from column H to QF on row 30 that is empty is to be hidden, every cell that is not empty is to be visible. I could double-click on any row and that is the row I need to hide the empty cells. I hope this is a better explanation.

**royUK** · Mar 1st 2021

You cannot hide individual cells only columns or rows

Adi64 · Mar 1st 2021

Roy, I should have said every column with an empty cell on row 30 will be hidden.

**Carim** · Mar 1st 2021

Hello,

To help everybody to visually understand your challenge ... why don't you attach a sample file to illustrate your point ...

**royUK** · Mar 2nd 2021

Maybe this

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rRng As Range
    Dim iX As Integer
    
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    
    On Error GoTo exit_proc
    Set rRng = Range("A30: ADO600").SpecialCells(xlCellTypeBlanks).Entirecolumn.Hidden=True
    Exit Sub
exit_proc:
    MsgBox "No blank cells found"
End Sub

Display More

Adi64 · Mar 2nd 2021

Carim, good advice. Please see the attached word document.

I thank you all for your continuing support.

**Carim** · Mar 2nd 2021

Great to have a very clear explanation with your Word document ...

Even better ... why don't you attach your Excel file ...this allow to test the proposed solution ...

**Carim** · Mar 2nd 2021

If you cannot attach your file ... hope you can adapt following macros ... to your specific situation

Code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Adjust the very last Column Letter of your Sheet and
' Replace Column X by whatever your Last Column Letter is .....
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Left Double-Click on any row to hide the corresponding Columns with Blanks ''''
If Target.Count > 1 Then Exit Sub
Dim i As Long
i = Target.Row
On Error Resume Next
Range("A" & i & ":X" & i).SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True
Cancel = True
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
' Right Click to make All Columns Visible ''''''''''''''''''''''''''''''''''''''
Columns("A:X").EntireColumn.Hidden = False
Cancel = True
End Sub

Display More

Hope this will help

**Carim** · Mar 3rd 2021

Once you have tested the macros ... feel free to share your comments

Adi64 · Mar 4th 2021

Carim, That worked so well. Thank you

**Carim** · Mar 4th 2021

Quote from Adi64

Carim, That worked so well. Thank you

Very happy to hear this is helping you out

Many Thanks for your Thanks ... AND for the Like

select cell have that row selected and hide columns that are empty.

Participate now!

Share

Users Viewing This Thread