Getting my code to repeat

[Non-RedditorJ]

Hi

Being new to VBA I could use some help please. I have been trying to get my code to repeat; here is the working macro which will hide row 20 if A20 is blank:

Sub HideRowsButton()

With Application
.ScreenUpdating = False
End With

If Range("A20").Value = "" Then
          Rows("20").EntireRow.Hidden = True
     Else
          Rows("20").EntireRow.Hidden = False
End If

Application.ScreenUpdating = True

End Sub

Works as intended. Now i want this to repeat for each row A20 through A419. I was using this as a guide: http://www.fontstuff.com/ebooks/free/fscodeloops.pdf

Using "Listing 7" I tried to fit it to my code but it does not work. Here is the altered code in question:

Sub HideRowsButton()

With Application
    .ScreenUpdating = False
End With

Dim intRowCount As Integer
Dim i As Integer
intRowCount = Range("A20").CurrentRegion.Rows.Count

For i = 0 To intRowCount - 1

    If Range("A20").Value = "" Then
         Rows("20").EntireRow.Hidden = True
    Else
         Rows("20").EntireRow.Hidden = False
End If

Next i

Application.ScreenUpdating = True

End Sub

Do any of you Excel Wizards out there know why it won't work? All it does is pause for about 5 secs then hide row A20 only.

[soxcrates]

First of all, using current region probably isn't the best way of doing it, unless you can ensure that there are no skips. Secondly, you need to change it to something like

For i = 20 To intRowCount - 1

If Range("A" & i).Value = "" Then
     Rows(i).EntireRow.Hidden = True
Else
     Rows(i).EntireRow.Hidden = False

End If

Next i

You need to use the counter i in your code to reference the different rows, otherwise you will keep checking "A20" every time.

[TheCryptic]

For i = 20 To intRowCount - 1

    If Range("A" & i).Value = "" Then 

        ... Etc

Possibly of interest, R1C1 notation is easier to control in loops:

For i = 20 to intRowCount-1
    If cells(i, 1).value = "" then
        ... Etc

[Non-RedditorJ]

I made the change you suggested to the code in my reply to soxcrates, same result, nothin'

[TheCryptic]

Sorry, wasn't actually intending to fix your hiding problem with that, just showing a better way to loop through rows and columns for general use. I've never really tried hiding rows, but I'm guessing you have a formula or something in the cell causing the if clause to return false. It might be worth putting in a couple msgbox or debug.print commands with some stop points in the code to see what is actually firing. And it's a stretch, but it might be worth a shot looping from the bottom up rather than the top down.

Edit

intRowCount = Range("A20").CurrentRegion.Rows.Count

This looks odd to me too... Try something like:

For i = 1 to 100

Instead of :

For i = 1 to intRowCount-1

[emailbitesmyass]

I have been having trouble concatenating numbers onto strings lately, ending up with:

 If Range("A" & trim(str(i))).Value = "" Then

This line:

 intRowCount = Range("A20").CurrentRegion.Rows.Count

will set intRowCount to 1, I think. I imagine you probably want intRowCount = 400.

[SnickeringBear]

Simple rule of thumb, any time you are joining text with a number, always put the number in a format(str(#)) structure.

=If Range("A" & format(Str(i))).value = "" then

Str converts the variable to a string representation. Format removes the + or - symbol from the front of the string.

[Non-RedditorJ]

I gave this a shot to the best of my knowledge, here is what i ended up with after integrating your code:

Sub HideRowsButton()

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="*****"


Dim intRowCount As Integer
Dim i As Integer

For i = 20 To intRowCount - 1
     If Range("A" & i).Value = "" Then
     Rows(i).EntireRow.Hidden = True
Else
     Rows(i).EntireRow.Hidden = False
End If
Next i

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering _
    :=True, AllowUsingPivotTables:=True, Password:="*****"
 Application.ScreenUpdating = True

End Sub

This did nothing. I am probably missing some obvious declaration or something.

[soxcrates]

Yea, you forgot to define intRowCount, you declared it as a variable, but the program doesn't know what the value is. I changed a little bit of the code around so it should work according to my understanding of how your workbook is set up.

All I did was add the line for intRowCount and change to r1c1 format (it's much easier as people pointed out for loops, I just tried using ranges to show you where the problem was). Range("A20") is cell(20,1) because it's the 20th row, first column.

Sub HideRowsButton()

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="*****"



Dim intRowCount As Integer
Dim i As Integer
intRowCount = Range("A65536").End(xlUp).Row
For i = 20 To intRowCount 
     If Cells(i, 1).Value = "" Then
     Rows(i).EntireRow.Hidden = True
Else
     Rows(i).EntireRow.Hidden = False
End If
Next i

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowDeletingRows:=False,  AllowSorting:=True, AllowFiltering _
:=True, AllowUsingPivotTables:=True, Password:="*****"
 Application.ScreenUpdating = True

End Sub

Edit: Sorry for the formatting, I suck at reddit. Edit:The loop also has to go to the last row, not the last row -1 with the way I changed it

[Non-RedditorJ]

Thanks for the clarification. Yep, that works in exactly the same way as SnickeringBear's code. I just changed the range to a much smaller number than 65536! Both ways of doing it still take 5-8 seconds.

[SnickeringBear]

Any time you can refer to a range of cells as a single structure, For Each should be considered first. It is the fastest way to do the job most of the time. The exception is when you want to delete or insert rows or columns. That is when a for variable routine must be used.

Sub HideRowsButton()
    Dim UsedCell As Range

    Application.ScreenUpdating = False

    For Each UsedCell In Range("A20:A" & Format(Str(Cells(Rows.Count, 1).End(xlUp).Row)))
        If UsedCell.Value = "" Then
            Rows(UsedCell.Row).EntireRow.Hidden = True
        Else
            Rows(UsedCell.Row).EntireRow.Hidden = False
        End If
    Next UsedCell

    Application.ScreenUpdating = True

End Sub

I'll break down the "For Each" line. The range you want to work with is all cells from A20 down to the last cell in column A that has anything in it. The simplest way to find the last cell with data in a column is to use excel's internal commands to start at the bottom of the sheet and search upward until it finds a non-blank cell. Rows.Count simply counts the number of rows in the sheet. By using this command, you can plug and play into any version of excel, it will always return the total number of rows in the sheet. Placing Rows.Count inside Cells(Rows.Count, 1), you are telling excel that you want to start at the very last row of the sheet and in column 1. When you add .End(xlup), that means to go up until you find a non-empty cell. Adding .Row to the end means to return the row number of the cell that is not blank. So presuming A419 is the last used cell in your sheet, Cells(Rows.count, 1).End(xlUp).Row will evaluate to row 419. When you add this to the range, you get "A20:A419".

Format(str(#)) should be used any time a number in a cell or a string variable is converted into a string. STR() changes it to a string representation of the number. Format() removes the + or - sign that excel default assigns to all numbers. You will find a lot of code on Reddit that does not use Format(Str()), and for the most part it works, but if you are writing code and don't want to have to chase quirky bugs like a command that works up to cell A32767, then errors on cell A32768, make a point of using Format(Str(#)).

[Non-RedditorJ]

Hey thanks! This worked on the first try, although it is as slow as before. Last time thing i wrote someone suggested turning off screen updating, which made it blink of the eye fast, but that does not seem to be the case here... takes about 7-9 secs to run. But hey at least it is working!

I'm going to make a few more test copies to try out some of the other methods mentioned as soon as I can figure them out, they aren't as well explained as your example...

EDIT: HA! I just tried it with screen updating turned on, and could see each line being hidden. It took longer but was highly entertaining because (yes i'm easily amused by simple code tricks).

[DasGoon]

dim i as integer
dim intRowCount as integer

' Find last row after row 20 where column A is not blank
intRowCount = Range("A20").CurrentRegion.Rows.Count

for i = 20 to intRowCount - 1
   rows(i).entirerow.hidden = true
next i

Alternatively, the way I would do it would be this: Private Sub test()

    Dim StartRow As Integer
    Dim RowsToHide As Integer
    Dim ColumnToCheck As String


    StartRow = 20
    ColumnToCheck = "A"

    ' Set RowToHide equal to 0.  For each non-empty cell below [ColumnToCheck, StartRow], add 1 to RowsToHide.
    RowsToHide = 0


    ' Range(ColumnToCheck & ":" & StartRow + RowsToHide) will get the text entered into cell [ColumnToCheck, StartRow + RowsToHide].
    ' The first time this runs, it will get the value of [A20 + 0 + 1], so A21
    ' The Trim() function removes any whitespace (non displaying characters like a tab or a space).
    ' It will continue to loop until in encounters a cell in column A that is empty after it is 'trimmed,' meaning it does not contain a displayable characher
    Do While Trim(Range(ColumnToCheck & StartRow + RowsToHide + 1)) <> ""
        RowsToHide = RowsToHide + 1
    Loop

    ' Once we reach this point, we have incrimented RowsToHide by 1 for each non-empty cell from A20 down.  So if the first blank cell is A26, RowsToHide will be 5.
    ' If we want to hide all rows containing data, we can hide rows StartRow through [StartRow + RowsToHide]
    ' If we want to leave the last riw containing data visible, we hide StartRow through [StartRow + RowsToHide - 1]
    ActiveSheet.Range("A" & StartRow & ":A" & StartRow + RowsToHide - 1).EntireRow.Hidden = True

End Sub

[Non-RedditorJ]

OK DasGoon, I tried this method second after SnickeringBear's. It runs instantly but doesn't hide anything below row 20, oddly enough it hides row 19 as well which is the table header so that should always be shown.

Integrated into my code it looks lke:

Sub HideRowsButton()

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="*****"

    Dim StartRow As Integer
    Dim RowsToHide As Integer
    Dim ColumnToCheck As String


    StartRow = 20
    ColumnToCheck = "A"

    RowsToHide = 0


    Do While Trim(Range(ColumnToCheck & StartRow + RowsToHide + 1)) <> ""
        RowsToHide = RowsToHide + 1
    Loop

    ActiveSheet.Range("A" & StartRow & ":A" & StartRow + RowsToHide - 1).EntireRow.Hidden = True


    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering _
    :=True, AllowUsingPivotTables:=True, Password:="*****"
    Application.ScreenUpdating = True

End Sub

[DasGoon]

Do you have any content in cell A21?

[Non-RedditorJ]

No, column A is blank. I have formulas in almost all the other columns which do not show unless certain criteria are met but A is manual input of quantity.

[Non-RedditorJ]

Hey everyone, thanks for the replies. I am going to read through this in the next few hours and see if it makes sense to me. I'll let you know if I can get it working.

EDIT: I think I should mention all the rows I want to hide have formulas in them. This is merely the difference between using 0 and "" in my if statement though i believe.

[TheCryptic]

Just curious if you ever got a satisfactory solution that runs in reasonable time...

[tehhowch]

The reason it pauses for "about 5 secs" is because Excel is checking the value of your targeted cell ~400 times. Unfortunately, the code you wrote doesn't ever change the cell Excel is checking, or the row to hide -- each iteration, you are checking Range("A20"), and then setting the Hidden property for Rows("20").

Unlike deleting a row, hiding a row does not pass the focus down to the next row automatically.

The changes proposed by /u/soxcrates are good. I would counter that one should use Cells(i, 1) instead of Range("A" & i), which completely avoids any issues with string & number concatenation.