run macro on non active sheet

[jtodd]

Hi All

I have this bit of code Sub HideOut()
Sheets("RDC_OB").Select
BeginRow = 208
EndRow = 244
ChkCol = 10

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End With
End Sub

How do I make this run from Sheet "Dash" wile staying on Sheet "Dash"
Sure this is simple but I cannot make it work !

 

[Swapnil Shah]

[COLOR=#333333]BeginRow = 208[/COLOR][COLOR=#333333]
EndRow = 244[/COLOR]
[COLOR=#333333]ChkCol = 10[/COLOR]

[COLOR=#333333]For RowCnt = BeginRow To EndRow[/COLOR]
[COLOR=#333333]If [/COLOR][COLOR=#333333]Sheets("RDC_OB").[/COLOR][COLOR=#333333]Cells(RowCnt, ChkCol).Value = 0 Then[/COLOR]
[COLOR=#333333]Sheets("RDC_OB").[/COLOR][COLOR=#333333]Cells(RowCnt, ChkCol).EntireRow.Hidden = True[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Sheets("RDC_OB").[/COLOR][COLOR=#333333]Cells(RowCnt, ChkCol).EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next RowCnt[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End Sub

[/COLOR]

It seems you have posted only part of the code as there is no "with" statement but you have "end with ". So not tampering with your code just appending sheet name in front of cell references.

This should do the trick.

Thanks
Swapnil Shah

 

[GTO]

Greetings,

Something like:

Option Explicit
  
Sub HideOut()
Dim BeginRow As Long
Dim EndRow As Long
Dim ChkCol As Long
Dim RowCnt As Long


  BeginRow = 208
  EndRow = 244
  ChkCol = 10
  
  With ThisWorkbook.Worksheets("RDC_OB")
    For RowCnt = BeginRow To EndRow
      If .Cells(RowCnt, ChkCol).Value = 0 Then
        .Cells(RowCnt, ChkCol).EntireRow.Hidden = True
      Else
        .Cells(RowCnt, ChkCol).EntireRow.Hidden = False
      End If
    Next RowCnt
  End With


End Sub

Just to mention, empty cells will evaluate to zero.

Hope that helps,

Mark

 

[jtodd]

Hi Sorry the End with should not have been there
I now have this
Sub HideOut()
BeginRow = 208
EndRow = 244
ChkCol = 10

For RowCnt = BeginRow To EndRow
If Sheets("RDC_OB").Cells(RowCnt, ChkCol).Value = 0 Then
Sheets("RDC_OB").Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt

End Sub

When I run this on cell update from worksheet "Dash" runs very slowly , seems like it calculates it one line at a time?

 

[jtodd]

Thanks that works fine but very slowly , seems to do one row at a time.
When I run the macro with the RDC_OB activated it works very fast ?

 

[Swapnil Shah]

You can use following code

Sub HideBlank()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim cell As Range


BeginRow = 208
EndRow = 244
ChkCol = 10


With Sheets("Dash")
    For Each cell In .Range(.Cells(BeginRow, ChkCol), .Cells(EndRow, ChkCol))
        If cell.Value = 0 Then cell.EntireRow.Hidden = True
    Next cell
End With


End Sub

Thanks
Swapnil Shah