Hiding/Unhiding Rows Loop

[jumbo1979]

I have a data set that is dynamic and refreshes every 5 seconds. I would like to do several things to this data set.

1. Hide any rows that have a value of 0 in column B
2. Unhide any rows that have a value <>0 in column B
3. Sort the remaining unhidden columns descending by column B
4. Repeat this whole process every time the data changes

Here is a sample of my data:

Country P&L Bps
Japan -9,206,300 (0.0037)
United States -1,819,537 (0.0007)
Germany -364,430 (0.0001)
Taiwan -26,211 (0.0000)
Singapore -17,596 (0.0000)
Canada -7,421 (0.0000)
Malaysia -3,493 (0.0000)
Argentina 0 0.0000
Argentina 0 0.0000
Austria 0 0.0000
Bangladesh 0 0.0000
Belgian Francs 0 0.0000
Belgium 0 0.0000
Belize 0 0.0000

It might be helpful to note that column B data is a formula reading off of another worksheet (=SUMIF('All Data'!$Z$2:$Z$10009,"MAL",'All Data'!$D$2:$D$10009))

I hope someone can help. Everything I try is to no avail. Thank you for your time.

 

[SkipVought]

Hi,

Have you tried using the AutoFilter and the Macro Recorder?

Post back with the code you are having trouble with.

Skip,
[sub]
[red][/red]
[/sub]

 

[jumbo1979]

this does not work:

Sub Update()


Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="0"
ActiveCell.Offset(8, 0).Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True
Selection.AutoFilter Field:=2
Selection.AutoFilter
End Sub

 

[SkipVought]

[tt]
Country P&L Bps
[/tt]
2. Unhide any rows that have a value <>0 in column B

Activesheet.showalldata

1. Hide any rows that have a value of 0 in column B

     Activesheet.[A1].AutoFilter Field:=Activesheet.cells.find("P&L), Criteria1:[red][b]<>[/b][/red]"0"
 .

3. Sort the remaining unhidden columns descending by column B
where is your code for this?
4. Repeat this whole process every time the data changes
right-click the sheet tab - select View Code
Select Workbook in Object Dropdown
Select Change in Procedure Dropdown

Private Sub Worksheet_Change(ByVal Target As Range)
   YourMacroName
End Sub

Skip,
[sub]
[red][/red]
[/sub]

 

[jumbo1979]

OK - so I got to this code:

Sub UnHide_Hide()

Application.ScreenUpdating = False

Dim i
For i = 1 To 130
With Intersect(Columns(2), ActiveSheet.UsedRange)
Rows(i).Select
Selection.EntireRow.Hidden = False
If .Rows(i).Value = 0 Then
Selection.EntireRow.Hidden = True
End If
End With
Next

Application.ScreenUpdating = True

End Sub


Which works - but how do i make the length of the rows dynamic instead of stuck at 1 to 130? And also how do I add sorting into there?

 

[SkipVought]

I would not use UsedRange, as you can get unexpected results if you are DELETING rows of data.

dim lLastRow as long, lRow as long
llastrow = sheetobject.cells(sheetobject.cells.rows.count, 2).end(xlup).row  'using column 2 per your example

for lrow = 1 to llastrow
  with sheetobject.cells(lrow, 1)
     .entirerow.hidden = false
     '???
     .entirerow.hidden = true    
  end with
next

Skip,
[sub]
[red][/red]
[/sub]

 

[jumbo1979]

Not sure I am running this right. Does this look correct?

Sub Test()

Dim lLastRow As Long, lRow As Long
For lLastRow = sheetobject.Cells(sheetobject.Cells.Rows.Count, 2).End(xlUp).Row
For lRow = 1 To lLastRow
With sheetobject.Cells(lRow, 1)
Selection.EntireRow.Hidden = False
If .Rows(lRow).Value = 0 Then
Selection.EntireRow.Hidden = True
End If

End With
Next
End Sub

 

[SkipVought]

1. Your sheetobject will be something like

Sheets("MySheetName")
or
Sheet1   'the sheet CodeName in te VB Editor Project Explorer

2.

  If .Rows(lRow).Value = 0 Then

is a problem. .Rows(lRow) is a RANGE and cannot be equaed to one value. What are you trying to do?

Skip,
[sub]
[red][/red]
[/sub]

 

[jumbo1979]

I have a data set that is dynamic and refreshes every 5 seconds. I would like to do several things to this data set.

1. Hide any rows that have a value of 0 in column B
2. Unhide any rows that have a value that is not 0 in column B
3. Sort the remaining unhidden columns descending by column B
4. Repeat this whole process every time the data changes

Here is a sample of my data:

Country P&L Bps
Japan -9,206,300 (0.0037)
United States -1,819,537 (0.0007)
Germany -364,430 (0.0001)
Taiwan -26,211 (0.0000)
Singapore -17,596 (0.0000)
Canada -7,421 (0.0000)
Malaysia -3,493 (0.0000)
Argentina 0 0.0000
Argentina 0 0.0000
Austria 0 0.0000
Bangladesh 0 0.0000
Belgian Francs 0 0.0000
Belgium 0 0.0000
Belize 0 0.0000

The number of rows changes every 5 seconds therefore the code need to be dynamic and not static.

 

[PHV]

data set that is dynamic and refreshes every 5 seconds
Where the data are coming from ?
In SQL it's very easy:
SELECT Country, P&L, Bps FROM someTable WHERE P&L<>0 ORDER BY P&L

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jumbo1979]

The cells in the worksheet with the data I want to manipulate are sumif formulas grabbing data from another worksheet in the book.

 

[firefytr]

The usedrange does not give you errors if deleting, it's if you iterate forwards that gives you erroneous results. Anytime you INSERT or DELETE anything, you must iterate through the loop BACKWARDS to prevent those erroneous results, you'll skip ranges if you do not.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci