Loop through named range? 2

[lespaul]

As one of Dale's projects, I have learned the uses of named ranges and database functions. However, I am trying to modify one of his spreadsheets and if you are not that familiar with Dale's work, it can be hard for someone who's not really familiar with VBA.

So anyway, I have named a range in my report 'JurorPay'. I would like to loop through this named range and if any cell is = to zero, then hide or delete (either way) the row.

Any pointers?

Thanks! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SBendBuckeye]

I believe this should be close but I cannot test it.

Public Sub glw()

Dim OneCell As Range

For Each OneCell _
'Move through A1 thru A7
In Worksheets(1).Range(Cells(1, 1), Cells(7, 1))
MsgBox OneCell.Address & " " & OneCell.Value
If OneCell.Value = 0 Then
'Do something here
End If
Next OneCell

End Sub

Hope this helps get you started!

Good Luck!

 

[rmikesmith]

Leslie,

Here's a slightly modified version of SBB's procedure that uses the named range you referenced:

Public Sub glw()

Dim OneCell As Range

   For Each OneCell In

Range("JurorPay")

MsgBox OneCell.Address & " " & OneCell.Value
         If OneCell.Value = 0 Then
            'Do something here            
         End If
   Next OneCell

End Sub

Regards,
Mike

 

[Zathras]

There is one problem with the macros as presented. If you merely hide the rows it is ok, but since you indicated that you might want to delete the row, there is a "gotcha": If you have two rows in succession the second one does not get deleted. Example, if you delete row 5, then row 6 becomes row 5, and if it is zero, tough luck, row 5 has been deleted already and the next row to be considered is row 6 (formerly row 7).

There is also some question of what would be appropriate if one column in the row is zero and another is non-zero. Consequently, the range should be limited to a single column.

Therefore, I offer this as an alternative:

Public Sub DeleteZeroRows()

' Deletes rows with zero (or blank) value

Dim OneCell As Range
Dim RowsToDelete(1000) As Long
Dim i As Integer
Dim j As Integer

' Only safe if a single column is specified

  If Range("JurorPay").Columns.Count > 1 Then
    MsgBox "Range ""JurorPay"" has more than one column.  Job Cancelled."
  Else
    i = 0
    For Each OneCell In Range("JurorPay")
      If OneCell.Value = 0 Then

        ' Remember this row and delete it later

        i = i + 1
        RowsToDelete(i) = OneCell.Row
      End If
    Next OneCell
    If i > 0 Then
      For j = i To 1 Step -1
        Range("A" & RowsToDelete(j)).EntireRow.Delete
      Next j
    End If
  End If
End Sub

 

[lespaul]

Ok, then my other choice is to do the 0 pay check before creating the report. My Delphi application creates an OLEVariant array that I then copy to the "Data" sheet in my workbook. Then I run a (Dale) macro that generates the completed report. First it counts the number of rows on the data sheet, then copies the formula from the 1st row of the report to the appropriate number of rows and the formula in each row references the data sheet cells to put the information in the proper format for printing. I was thinking about making the count function conditional (only if juror pay > 0) but the data would still be on the data sheet and I would be missing some of the end of the report information. So that's when I thought about hiding or deleting the 0 juror pay rows.

I'm open to suggestions! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

Sounds like your design is ok. I was merely suggesting a technical correction in part of the implementation.

 

[bryanbayfield]

Zathras:There is one problem with the macros as presented. If you merely hide the rows it is ok, but since you indicated that you might want to delete the row, there is a "gotcha": If you have two rows in succession the second one does not get deleted. Example, if you delete row 5, then row 6 becomes row 5, and if it is zero, tough luck, row 5 has been deleted already and the next row to be considered is row 6 (formerly row 7).

Top tek tip for you. If you start processing at the bottom row and move upwards you obviate this problem.

 

[Zathras]

Yes Bryan, that is how I do it when I am starting fresh. But I was trying to keep it simple for lespaul who is just learning the basics of "for each c in range."

I'm sure that you would agree that the loop control code is a little more obscure to go from bottom to top and I didn't see any reason to rewrite it completely when it was possible merely to build on what has gone before by adding another simple "for" construct.

I also purposely side-stepped the issue of having to ReDim the array by setting it large enough (I hope) to handle all of the potential deletes.

 

[bryanbayfield]

I'm a fairy, my name's Nuff.

Fairy Nuff!

 

[xlbo]

2nd tek-tip
The For i = x to y functionality can be customised in a coupla ways using the Step keyword. this determines how i increments

ie for i = 1000 to 2 step -1
is ideal for deleting rows as the rowcount does not get affected by the deletions

and for i = 2 to 200 step 2
will increment i in 2's (as opposed to the default of 1) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[lespaul]

zathras,

I pasted your code into my workbook and it seems to be ok, until the first time it has to put a row into the RowsToDelete. The value of i = 1001 which gives me a subscript out of range error. Why is the value of i 1001 instead of 1?

Thanks!
Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

I was afraid that might happen. The macro is trying to delete more than 1,000 rows of zero or blank data. What exactly is the range definition of "JurorPay"? If it is the entire column (e.g. $B:$B) then all 65,536 rows will be scanned and several thousand rows will need to be deleted. -- Not very practical.

On the other hand, if "JurorPay" is just the range where you really have data, then increase the array size to a value large enough to accomodate the number of jurors with no pay for which you need to delete lines.

If that's not possible, then it is time to re-write the macro from scratch, using the basic approach outlined by bryanbayfield.

I am going on vacation for eight days starting in just a few hours, so you will have to find someone else (Bryan? Geoff?) to help you if you can't finish it yourself.

Another fix would involve comparing the row number of the "OneCell" range with the last row number (note NOT the number of rows -- that is a different thing) of the UsedRange of the worksheet to know when to exit the loop and stop the macro.

There is one other possiblity: Revert to Mike's code and just run it several times. (Say 10 or 20) That way the skipped row problem goes away. (It would be possible to construct a series of zero and non-zero lines that would allow one or more zero lines to remain, but it would be very unlikely to happen in the real world.)

 

[lespaul]

I currently have the entire column named "JurorPay", but I think I can figure out how to name just the used cells. I'll look into it tomorrow.

thanks for all your help.

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!