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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,261
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,628
Messages
5,832,762
Members
430,165
Latest member
Idjaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top