Row VBA questions.

[freaknyea]

Sub hiderowsifnodata()
Rows.Hidden = False
For i = 406 To 20 Step -1
If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True
Next i
End Sub

I am having some problems with this code.

What I am trying to do is hide (prefer to collapse) rows that have no data after column A and B.
Column A and B will always have data.
I prefer to use this code with any selection I choose. I believe right now it is for a set range.

What am I doing wrong?

[RonMcK]

I believe that you need to declare the variable i.


Option Explicit
Sub hiderowsifnodata()
Dim i As Long
Rows.Hidden = False
For i = 406 To 20 Step -1
Debug.Print i, Application.CountA(Rows(i))
If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True
Next i
End Sub
I have a couple of questions:
Do you only want to hide rows in the range of rows 20 and 406?
Should your code verify data in columns A & B before it accepts hiding a row with fewer than 3 non-empty cells?Ron

[freaknyea]

It still didn't work. I don't know what the heck is going on.

[mikerickson]

You can either hide all of a row or show all of a row.
Excel will not allow you to hide only some cells in a row.

[Bob Phillips]

The code is fine, so what are you trying to do and what do you mean by doesn't work.

[freaknyea]

I want this:

A B C D
1 Data Data Data Data
2 Data Data Data Data
3 Data Data Data Data
4 Data Data
5 Data Data
6 Data Data
7 Data Data Data Data
8 Data Data Data Data
9 Data Data Data Data



To look like this:



A B C D
1 Data Data Data Data
2 Data Data Data Data
3 Data Data Data Data
4- >Data Data
7 Data Data Data Data
8 Data Data Data Data
9 Data Data Data Data


But!!! It doesn't have to be A1 and beyond I would like to choose a region. Right now I believe it is A20:M406.

[RonMcK]

As Bob (er, Xid) says this works. Enclosed is a small workbook with the macro in it. When you open it you will see that 62 rows have been collapsed down to 13 rows with data either in the header or in a column to the right of Col B. Highlight rows 1 to 62, do Format > Rows > Unhide and try the macro yourself. It should work.

Ron

[RonMcK]

Let's see, your new example suggests that you want to hide all but 1 (one) empty row. Am I reacing you right?

What about the answers to my other two questions?

New questions, do you want to mark a range (first to last rows to look at) and have the macro only operate on those rows?
Or, do you want to have the macro pop up a user form where you enter the first and last row?

Thanks,

Ron

[freaknyea]

I have a couple of questions:
Do you only want to hide rows in the range of rows 20 and 406?At this time yes. In the future, in case I add rows, it would be nice to have a macro work depending on which rows I select. I am fine with updating the macro everytime, but obviously the easier the better:).

Should your code verify data in columns A & B before it accepts hiding a row with fewer than 3 non-empty cells?Actually that is a good question. I never really thought about that. I believe what you are saying is that I could select the entire worksheet and if it didn't have matching data and column C and beyond was empty then it would not hide the row. The issue here is that each row has different data in columns A and B. And that would be alot of information to verify.

New questions, do you want to mark a range (first to last rows to look at) and have the macro only operate on those rows?By mark, do you mean a selection? If so, yes.

Or, do you want to have the macro pop up a user form where you enter the first and last row?I like this one best.



Let's see, your new example suggests that you want to hide all but 1 (one) empty row. Am I reacing you right?


Orginally, I wanted to Collapse the row. There are some consecutive rows, beyond column A and B, that are empty. Other rows are scattered throughout. I can't sort it any different than I have it now, so I can't put all the blank rows together. The information is sorted based on column B.
Hiding the cells is just as good. This is a summary sheet between a Start:End!. So in the case that a worksheet within the Start:End! is updated and happens to be a blank row on the summary, will the row appear on the summary sheet without me unhiding the rows and then rehiding them again?

I hope this helps. I do appreciate the assistance.

[freaknyea]

The macro works fine on the sheet you attached... I tried it on mine and it didn't work...

[RonMcK]

The macro works fine on the sheet you attached... I tried it on mine and it didn't work...
Suggestion: Open the VBE with a pane (or window) for your code module and another for mine (make 'em thin and tall), line them up next to each other so you can compare your code with mine, line by line, to spot the difference(s).

About yours not working: did you wait long enough for it to run? Even doing the small set I have in my worksheet takes a minute or so, extrapolate since you have (406-20)/50 times as many lines to test and potentially hide.

I'll look at your other answers and questions later this evening after I get home.

Ron

[RonMcK]

The following is your original code with some extra stuff so you can enter the begin and end rows for hiding at runtime. I included some error checking to make it 'user friendly'.

Option Explicit
Sub hiderowsifnodata()
Dim i, BegRow, EndRow, LastRow As Long
Dim Msg1, Msg2, Msg3, Style1, Style2, Title, Response As String
Msg1 = "Row must be less than last (highest) row used." + vbCrLf + "Please Try Again."
Msg2 = "Row must be equal to or greater than the first row you selected." + vbCrLf + "Please Try Again."
Msg3 = "Row entered is greater than last (highest) row use." + vbCrLf + "I am changing your Ending Row to " + Str(LastRow) + vbCrLf + "Do you want to use this as your last row? (Yes/No)"
Style1 = vbOKOnly + vbExclamation + vbInformation
Style2 = vbYesNo + vbQuestion
Title = "Whoops!"

LastRow = GetLastRow()
Get_Begin_Row:
BegRow = InputBox("Enter number of first row in range to hide", "Begin Hidden Range", "")
If BegRow > LastRow Then
Response = MsgBox(Msg1, Style1, Title)
GoTo Get_Begin_Row
End If
Get_End_Row:
EndRow = InputBox("Enter number of last row in range to hide", "Begin Hidden Range", "")
If EndRow < BegRow Then
Response = MsgBox(Msg2, Style1, Title)
GoTo Get_End_Row
End If
If EndRow > LastRow Then
Response = MsgBox(Msg3, Style2, Title)
If Response = vbNo Then
EndRow = 0
GoTo Get_End_Row
Else
EndRow = LastRow
End If
End If

Rows.Hidden = False
For i = EndRow To BegRow Step -1
' Debug.Print i, Application.CountA(Rows(i))
If Application.CountA(Rows(i)) < 3 Then Rows(i).Hidden = True
Next i
End Sub

Public Function GetLastRow() As Integer
GetLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
Searchorder:=xlByRows).Row
End Function

HTH,

Ron
Orlando, FL

[RonMcK]

And, here is a copy of the workbook.

Ron

[RonMcK]

Here is a copy of the workbook with the VBA updated to include an error trap for negative line number entries.

Ron

[freaknyea]

I may know what is going wrong and all of you may want to slap me on the back of the head.

There are Formulas in basically every blank cell...


I uploaded an attachment example of this sheet.

:dunno

[RonMcK]

I may know what is going wrong and all of you may want to slap me on the back of the head.

There are Formulas in basically every blank cell...


I uploaded an attachment example of this sheet.

:dunno

Well, the file didn't make it. Please try again. Watch the msgs that the browser is giving you; you may need to post a dummy msg or two so your msg count is greater than zero.

Ron

[freaknyea]

Dummy 1

[freaknyea]

Dummy 2

[freaknyea]

By the way, when you say didn't make it, do you mean you are unable to open it?

[RonMcK3]

By the way, when you say didn't make it, do you mean you are unable to open it?
No, it wasn't there. All that I saw was a message that you'd been unable to upload it (or something of that sort).

Anyway, I looked at your worksheet and decided that the following rework of your basic code will do the job. This assumes that the entries in the rows are all positive numbers. If this is true, then, the code will hide any row that has a zero total in column "N". I also set your current first and last rows as defaults for their respective InputBoxes.

Rows.Hidden = False
TestColumn = "N" ' Adjust as needed
For i = EndRow To BegRow Step -1
' Debug.Print i, Application.CountA(Rows(i)), Cells(i, "N")
If Cells(i, "N") = 0 Then Rows(i).Hidden = True
Next i
Accompanying is an updated copy of your example.xls.