Trying to selectively show/hide rows based off cell value via basic macro

[DaveLO1]

Hello,
I apologize if I am missing something obvious, I am new to this and am trying to figure out what I need to do.

I have a spreadsheet where I am trying to selectively hide rows based off value of 0 in column B, and then unhiding when there is a value entered (string). The purpose of these extra rows is for placeholder rows necessary for adding future values for linked arrays from another sheet, and I want to hide these rows from the user until there is data in them.

Here is the code I wrote with a friend who helped me get this functionality-

sub HideRows
sheet = ThisComponent.Sheets(0)
selectedRange = ThisComponent.getCurrentSelection()

dim cell as object
dim row as Integer, col as Integer
	For row = 0 to SelectedRange.Rows.getCount() - 1
	col = 2
	cell = selectedRange.getCellByPosition(col, row)
	REM hide row based on condition of cell value
	If cell.value = 0 Then
		selectedRange.Rows.GetByIndex(row).IsVisible = false
		
	Endif
	Next Row
End Sub

This will currently work when selecting rows manually, and will hide all rows regardless of value, I believe the error with that to be the value = 0 part. I want to be able to run this on a per-sheet basis that will query rows individually and look for values in column B to determine whether to hide or not.

To do this one at a time, I need to define the range of rows I want to work with, and have it loop and increment by row until reaching the last row (to have it look at one at a time), is this correct? If I can get the conditional hiding right, a

else
		selectedRange.Rows.GetByIndex(row).IsVisible = true

should work to unhide the rows that are already hidden once they have values, I think.

Sincerely thank you for the help and pointers in the right direction.

EDIT: Now have this based off a link below. This is hiding all 0s, but it is also hiding product SKU strings (combination of letters and numbers, no 0’s though). It is not hiding non-0 integers, though.
How should I modify for it to not affect strings other than “0”?
Thank you

sub HideRows
Call HideRowValue(1, 0)
end sub

Sub HideRowValue( iColumnIndex As Integer, oValueToHide As String)
 Dim  oSheet As Object, oCell As Object
	oSheet=thiscomponent.getcurrentcontroller.activesheet

REM Traverse Rows:  99999= set here your maximum number of Rows to check...
For i = 130 to 155
    oCell = oSheet.getCellByPosition( iColumnIndex, i )
    If oCell.Value = oValueToHide Then
        oSheet.Rows.getByIndex( i ).isVisible = False
        
    End If
Next i

End Sub

[JohnSUN]

Welcome! Would you like to use a filter for this purpose? The result will be the same as described by you. You don’t want? Then use the @librebel macro from the same topic

[DaveLO1]

@JohnSUN, Thank you for your suggestion. I had a similar thing for a different purpose on this workbook that I had tried solving with a macro until I realized I could just use a filter for that. For this though, I would like to use a macro to show/unhide. I think I would have to unhide manually for using a filter, and this would have to be done on many sheets, so I think it might be easier to run a macro to do so for me, assuming I can get it working right!
I am having trouble finding @librebel macro you referenced, do you happen to have a link?
Thank you sir.

[JohnSUN]

Sorry, words “use a filter” was a link on this question - hide row based on passed date I don’t understand why the link is not working!

[DaveLO1]

@JohnSUN, thank you! I got it somewhat working off that. I have updated the OP with what is going on now and the code. It is now hiding rows with complex strings in that column, but it is not hiding non-0 integers. Any idea what I should do?
Thank you sir.

[JohnSUN]

oSheet.Rows.getByIndex( i ).isVisible = (oCell.getString() = oValueToHide)? Without IF...Then

[DaveLO1]

Changing from “If oCell.Value” to “If oCell.getString()” did it! That makes complete sense. Thank you!

[DaveLO1]

This did it! Note the use of oCell.getString for matching string value. This will show and hide when ran based values in column B (1), looking for a string equal to “0”, for rows 10-300 on the currently selected worksheet.

sub HideRows
    Call HideRowValue(1, 0)
end sub

Sub HideRowValue( iColumnIndex As Integer, oValueToHide As String)
    Dim  oSheet As Object, oCell As Object
    oSheet=thisComponent.getCurrentController.activeSheet
    For i = 10 to 300
        oCell = oSheet.getCellByPosition( iColumnIndex, i )
        If oCell.getString() = oValueToHide Then
            oSheet.Rows.getByIndex( i ).isVisible = False
    Else
	oSheet.Rows.getByIndex( i ).isVisible = True
        End If
    Next i
End Sub

[KamilLanda]

You can also use method goToEndOfUsedArea to get the count of used rows in the sheet

Sub hideRows 'hide the rows as per string in the cell
	dim oDoc as object, oSheet as object, oCur as object, iRows&, i&, oCell as object, s$
	oDoc=thisComponent
	oSheet=oDoc.CurrentController.ActiveSheet 'get active sheet
	oCur=oSheet.CreateCursor 'cursor
	oCur.gotoEndOfUsedArea(false) 'cursor to the end of data in active sheet
	iRows=oCur.RangeAddress.EndRow 'count of the rows
	for i=0 to iRows 'walk through all rows
		oCell=oSheet.getCellByPosition(1,i) 'cell
		s=oCell.string 'string from cell
		if s="0" then 'hide row if string is "0"
			oSheet.Rows.getByIndex(i).isVisible=false
		end if
	next i
End Sub