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”?
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