Unhide cells to copy them then hide them again

[danielpalfrey]

Hi guys. As the title says. I am looking to unhide columns so that I can search them for a value. The cells matching the criteria are then copied and pasted onto sheet two. I have the copy and past working fine. After pasting these columns all need to be hid again. So the part I need help with is a vba method to unhide all hidden columns and remember them. Then it needs to hide those same cells again when I am finished with them.

When I get home I will be able to post the code I have right now but the code for the columns unhiding and hiding again just doesn't work in the slightest

 

[Yongle]

You do not need to unhide columns to find a value with VBA

Test with this

Sub Find_A()
    MsgBox Sheets(1).Columns("D").Find("a").Address(0, 0)
End Sub

Type "APPLE" into D7 in first sheet, hide column D and run above macro - the value is found

You can also copy from hidden columns
- try this which puts value of found cell in A1 in 2nd sheet..

    Sheets(2).Range("A1") = Sheets(1).Columns("D").Find("a")

But there may be other reasons for unhiding the columns. Below is one way (Looks at columns A to Z).
- code below finds all hidden columns, unhides then together, and then hides them in one hit
- you may prefer to do eveything column by column

Sub Unhide_Then_Hide_Hidden_Columns_()
    Dim cel As Range, rng As Range
[COLOR=#008000][I]'identify hidden columns[/I][/COLOR]
    For Each cel In Sheets(1).Range("A1").Resize(, 26)
        If Columns(cel.Column).Hidden = True Then
            If rng Is Nothing Then Set rng = cel Else Set rng = Union(rng, cel)
        End If
    Next cel
    MsgBox rng.EntireColumn.Address, , "Hidden Columns"     'delete after testing
[I][COLOR=#008000]'unhide colums[/COLOR][/I]
    rng.EntireColumn.Hidden = False

[COLOR=#ff0000][I]' *** here is where you search copy and paste etc ***[/I]
[/COLOR]
[I][COLOR=#008000]'hide Columns[/COLOR][/I]
    rng.EntireColumn.Hidden = True
    
End Sub

 

[danielpalfrey]

I will test when I get home. The reason for in hiding is because I am using instr to find a currency symbol. Instr does not find in hidden cells