Another problem with my Excel prog.

  xania 17:54 01 Feb 2009

I'm now trying to print out the results of my earlier programming, but need to make sure that I only print out rows with actual data. The worksheet contains columns of items and these are all totaled into column C. Using the Record macro> facility on some dummy data I had already generated, I included a filter to take out all rows where Column C contained either zero or blank, and this resulted in the code line:

ActiveSheet.Range("$A$1:$N$473").AutoFilter Field:=3, Criteria1:=Array("1", _
"100", "150", "2", "300", "4", "500", "8"), Operator:=xlFilterValues

However, This only works on the current data where the values currently in Column C are blank, 0,100,150,2,300,4,500 or 8, but, of course, I could have ANY value in a cell and I want all these rows included. Can you suggest a way of doing this, please.

  xania 11:56 02 Feb 2009

Hope you're all coping with the weather. I'm trapped at home so I've done a bit of research myself and come across a useful sub routine which can hide a column based on the value of in a cell. i.e.

Sub HideColumnH1()
If Range("B4").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Columns("H").EntireColumn.Hidden = False
End If
End Sub

which will hide column H if B4 contains the value 0.

I would assume the same can be used for Rows by substituting 'Row' for 'Column'. However, I want to check over 450 rows and also up to 40 columns, and I don't fancy writing nearly 500 of these lines into a sub-routine.

There is another point. The rows to be hidden could be anywhere down, but once a blank column occurs I know all the rest will be blank i.e if cell H1 is blank I wont need to print beyond column G.

My objective at the end of the day is to be able to print out from a 470 x 40 worksheet only those rows and columns which contain useful information (typically about 30 x 15).

Many thanks

  VoG II 12:25 02 Feb 2009

If it is just column C that you are interested in try

Sub test()
Dim LR As Long, i As Long
With ActiveSheet
LR = .Range("C" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("C" & i)
If .Value = "" Or .Value = 0 Then
.EntireRow.Hidden = True
.EntireRow.Hidden = False
End If
End With
Next i
End With
End Sub

If it could be arrange that C only contained blanks (no zeros) then:

Sub test()
On Error Resume Next
ActiveSheet.Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
End Sub

  xania 14:06 02 Feb 2009

Wow, I'm trying to work out exactly what this does, but I can certainly confirm that it works. Thanks a bunch.

  Pineman100 14:28 02 Feb 2009

Don't try to understand the Excel workings of the mighty VoG™. Only he and God can do that.

Just use them gratefully.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?