hide selectively

[lerita]

I'm having some trouble solving it with my very limited VBA skills.

I have a spreadsheet with data (linked with formulas, so that it cahnges all the time). In order to export numbers to another program, I have to save it with a certain format, as a txt file.

I want to make a macro to hide several rows and columns so that then my other macro I can "select visible" and paste it ito a clean file to save as a text file..

For instance:
- Hide all rows that have 0 in certain columns.
- Hide all columns with a certain character in the first row. (S or C, depending on the value of a certain cell).

I have tried using autofilter (eg. take column BZ, filter 0, select all visible rows, hide selected) but then when I remove the filter, it shows all the rows, including the ones that have 0 in that column.

I know that this has to be easy, but I can't figure it out.

Thanks guys.

 

[Joe4]

Try this VBA code. Adjust your ranges/formulas as needed:

Sub MyHide()

    Dim myRowRange As Range
    Dim myColRange As Range
    Dim cell As Range
    
'   Hide all rows that have 0 in certain columns
'   Define which cells to look at to hide rows
    Set myRowRange = Range("A2:A200")
    For Each cell In myRowRange
        If cell = 0 Then cell.EntireRow.Hidden = True
    Next cell
    
'   Hide all columns with a S or C in them
'   Define which cells to look at to hide Columns
    Set myColRange = Range("B1:Z1")
    For Each cell In myColRange
        If (cell = "S") Or (cell = "C") Then cell.EntireColumn.Hidden = True
    Next cell
    
End Sub

 

[lerita]

awesome

Yes!!!!!

This is just perfect! I'm still working on it (I need to modify the code a little bit, because I actually simplified the problem when I described it). But you gave me exactly what I needed to know!

I'm new to VBA, macros and Mr Excel, but I'm loving it, someday I'll be able to solve someone else's problem.

Thanks.. :wink: