Within a macro, I’d like to select every 200 cells within a column without specifying the specific cell address so that once I hide them, select another 200 cells from the start of what’s been shown, rinse and repeat.
So instead of the value being “$A$1207:$A$1406” It could just be the $A column and the starting row that is visible.
Hello,
You can do this with queryVisibleCells().
Simple example:
Sub Snippet
Dim oSheets As Variant
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
Dim oCellRangeByPosition As Variant
Dim oObj1 As Variant
Dim sAbsoluteName As String
oSheets = ThisComponent.getSheets()
oCurrentController = ThisComponent.getCurrentController()
oActiveSheet = oCurrentController.getActiveSheet()
oCellRangeByPosition = oActiveSheet.getCellRangeByPosition(0, 0, 0, 99) 'Cell range A1:A99'
oObj1 = oCellRangeByPosition.queryVisibleCells()
sAbsoluteName = oObj1.AbsoluteName 'sAbsoluteName contains visible cells in range'
End Sub
Just set you cell range and use queryVisibleCells() to see what is not hidden. Use as basis for your selection.
Hello @ERas,
After hiding your previous block of cells, you could just call the following Basic macro to select the next block:
Sub column_SelectVisible()
REM Selects the first <lNumberOfCells> visible cells in column <lColumnIndex> in the current Sheet.
Const lColumnIndex As Long = 0
Const lNumberOfCells As Long = 200
Dim oDoc As Object : oDoc = ThisComponent
Dim oSheet As Object : oSheet = oDoc.CurrentController.ActiveSheet
Dim oColumn As Object : oColumn = oSheet.Columns.getByIndex( lColumnIndex )
Dim oRanges As Object : oRanges = oColumn.queryVisibleCells()
Dim oRange As Object : oRange = oRanges.getByIndex(0)
Dim oCursor As Object : oCursor = oSheet.createCursorByRange( oRange )
oCursor.collapseToSize ( 1, lNumberOfCells )
oDoc.CurrentController.select( oCursor )
End Sub
HTH, lib
If I were to cut and paste the content into a column, how would I write out that line? And would I just add a 1 to the lColumnIndex variable to get it to shift a column to the right?
yes… Column index 0 refers to Column A, Column index 1 refers to Column B, etc.
For this to work, the above macro should be copy-pasted into your Standard Basic Macro Library.
Then you could ( for example ) make a Toolbar button that invokes the macro whenever you click on the button.
So I’m garbage at programming this, but what line of code would I be writing to increment the column?
incrementing the column would require another approach; please make a new question for that, with a reference to this question.
@ERas Since this answer seems accepted by you (based upon you using it it another question), please mark the answer as correct by ticking the 
(upper left area of answer).