Dec 07 2022 11:31 AM
Hello,
I am trying to automatically hide rows that have a value of 0 but I am unable to do so. The columns that contain the data are in "L" and when running the code I found it hides all rows instead of the certain rows that contain 0. Is there a way to accomplish this? I have pasted some of the code below and highlighted in red is what I was trying to implement. Any help is appreciated and I can show some of the data in a screenshot if needed, thank you!
Sub Format_PNL()
'
' Format_PNL Macro
'
On Error Resume Next
range("A1:A4").Select
Application.CutCopyMode = False
Selection.Copy
range("K1").Select
ActiveSheet.Paste
With Selection.Font
.Size = 8
.Name = "Arial"
End With
Columns("A:J").Select
Selection.Delete shift:=xlToLeft
Columns("F:AA").Select
Selection.Delete shift:=xlToLeft
Columns("H:M").Select
Selection.Delete shift:=xlToLeft
range("A7:A1173").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G").Hidden = True
'Hide reserves
Dim lRow As Long
Dim FindRowNumber As Long
Dim Row As range
'Find Reserves Top row
Set FindRow = range("A:A").Find(What:="Reserves Fund", LookIn:=xlValues)
FindRowNumber = FindRow.Row
'Find Bottom Row
lRow = Cells(rows.Count, 1).End(xlUp).Row
'Delete Rows
rows(FindRowNumber & ":" & lRow).Select
Selection.Delete shift:=xlUp
'Hide 0 Rows
Dim x As Double
Dim RowFirst As Long
Dim RowLast As Long
Dim i As Long
RowFirst = 12
RowLast = 150
For i = RowFirst To RowLast
x = Cells(i, "L").Value
If x < 1 And x > -1 Then rows(i).Hidden = True Else rows(i).Hidden = False
Next
---------------------------------------------------------------------------------
Sub Run_Format()
'
' Run_Format Macro
'
Application.ScreenUpdating = False
Sheets("Paste PNL Here").Select
Selection.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
Application.CutCopyMode = False
Application.Run "Format_PNL"
'Delete Blank Rows
Dim r As range, rows As Long, i As Long
Set r = ActiveSheet.range("A1:M100")
rows = r.rows.Count
For i = rows To 1 Step (-1)
If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete shift:=xlUp
Next
'Runs Macros
Columns("X:X").ColumnWidth = 0.81
Sheets("Paste BS Here").Select
Selection.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
Application.Run "Format_BS"
Sheets(Array("Paste PNL Here", "Paste BS Here")).Select
Sheets("Paste PNL Here").Activate
Sheets(Array("Paste PNL Here", "Paste BS Here")).Copy
Sheets("Paste PNL Here").Select
Sheets("Paste PNL Here").Name = "PNL Report"
Sheets("Paste BS Here").Select
Sheets("Paste BS Here").Name = "Bal Sheet Report"
Sheets("PNL Report").Select
Sheets("PNL Report").Move After:=Sheets(2)
Sheets("Bal Sheet Report").Select
ActiveWindow.View = xlNormalView
Sheets("PNL Report").Select
ActiveWindow.View = xlNormalView
Sheets("Bal Sheet Report").Select
Application.ScreenUpdating = True
End Sub
Dec 09 2022 12:52 AM
Attached is an example file with hide rows at zero value.
You could incorporate it into your code or project.
Maybe it will help you further.
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Dec 09 2022 08:26 AM