hide selectively

lerita

New Member
Joined
Nov 16, 2005
Messages
3
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,523
Office Version
  1. 365
Platform
  1. Windows
Try this VBA code. Adjust your ranges/formulas as needed:
Code:
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

New Member
Joined
Nov 16, 2005
Messages
3
awesome

:biggrin: 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:
 

Watch MrExcel Video

Forum statistics

Threads
1,119,270
Messages
5,577,114
Members
412,768
Latest member
klig
Top