Hide specified columns via macro

[bluesurger]

Hi,

i want to hide specified columns in a WB.

The macro should ask which columns i want to hide.
These columns could be A,C,H or D, AA, AH.
So i need a request from the macro with column i want to hide.
This request should ask me as long i enter a name for a column.
If i do not enter any more, the macro should hide the selected columns.

Normaly the number of columns i have to hide is between 1 and 16.

      
' Select Row(s) for hide


Private Sub InputTest()

Dim InputDummy As String, Cancel As Boolean, strPrompt As String

strPrompt = "Please declare column to hide"
Do
    InputDummy = InputBox(strPrompt, "No column selected")
    Cancel = StrPtr(InputDummy) = 0
    strPrompt = "Please choose a column to hide an press OK"
Loop Until InputDummy <> "" Or Cancel

If Not Cancel Then
    
    Columns(InputDummy).Select
    Selection.EntireColumn.Hidden = True

Else
    MsgBox "You press 'Cancel'"
End If
       
       

End Sub

but how is it possible to choose more than 1 column?

Thank you

kind regards

[nathansav]

Why not record a macro doing it, then look at the code?

[bluesurger]

? record a macro while a other macro is working?
But i think i need a Loop which ask me for that until i do not enter any more column-name. Or isn't it so?

[nathansav]

No, record a macro hiding the columns and then stop recording and look at the code produced.

[bluesurger]

hy nathansav,

the hide of the columns is working. But i have to start the macro for everz column i want to hide.
So i think i should habe a loop in the code which does ask me untill i did not enter any more. Do you know what i mean?

[bluesurger]

@nathansav: How do you mean that?

Do you really think it works as you said?

[bluesurger]

any otherone an idea?

Thank you

[JOHN H. DAVIS]

Does this help?

Sub bluesurger()
Dim i As Long
Dim answer As Variant
For i = 1 To ActiveSheet.UsedRange.Columns.count
Columns(i).Select
answer = MsgBox("Do you want to hide this column?", vbYesNo)
Select Case answer
Case vbYes
    Columns(i).Hidden = True
Case vbNo
    GoTo zz
End Select
zz:
Next i
End Sub

[bluesurger]

Hi John,

no sorry.

I need a InputBox on which i can enter my columns i want to hide.

I use this solution at the moment>

Sub Ausblenden()
Dim Bereich
On Error GoTo ErrAus
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Bereich = InputBox("Columns to hide (C:F,H:H,...    ")
    If Bereich = "" Then GoTo Ende
    Range(Bereich).Select
    Selection.EntireColumn.Hidden = True
    GoTo Ende
ErrAus:
    MsgBox "Please enter correct area"
Ende:
End Sub

Sub Einblenden()
Dim Adr
    Adr = ActiveCell.Address
    Cells.Select
    Selection.EntireColumn.Hidden = False
    Range(Adr).Select
End Sub

With this ToggleB.

Private Sub ToggleButton1_Click()

Dim TB As ToggleButton

Set TB = ToggleButton1

If TB.Value = True Then

TB.Caption = "hide column"

Call Ausblenden

Else

TB.Caption = "unhide column"

Call Einblenden

End If

End Sub

[Mowgli2]

Hi Bluesurger,

On this workbook I have added a userform with each of the text boxes writing to a variable. These are then used to select which columns to hide. Let me know if it works for you

Hide_Columns.xlsm

[bluesurger]

HY Mowgli

i will test it tomorrow. thank you

[bluesurger]

Hi Mowgli,

this macro works good. Thank You

[Mowgli2]

Your welcome, dont forget to mark the thread solved

[bluesurger]

Ok, before solve the thread one last question.

What parts i have to change if i want to hide rows in the same way?
Can i also use this code and put "row" instead of "Column" inside?

Thank you

[Mowgli2]

The way it is set up in the form it is easier to modify the code slightly in a new form, I have done this and included both forms.

The columns form took the letter entered in a text box, added a "1" to the end and selected the cell to hide the entire column

Hide_Columns_Rows.xlsm

[bluesurger]

Thank you it works pretty well