Selecting unspecific cells in a macro

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 :heavy_check_mark: (upper left area of answer).