Ask Your Question

Selecting unspecific cells in a macro [closed]

asked 2018-03-17 02:39:13 +0200

ERas gravatar image

updated 2020-08-07 22:16:57 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-07 22:17:04.801264

2 Answers

Sort by » oldest newest most voted

answered 2018-03-17 05:29:35 +0200

librebel gravatar image

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 ) oCursor )
End Sub

HTH, lib

edit flag offensive delete link more


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?

ERas gravatar imageERas ( 2018-03-17 14:40:29 +0200 )edit

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.

librebel gravatar imagelibrebel ( 2018-03-17 15:01:46 +0200 )edit

So I'm garbage at programming this, but what line of code would I be writing to increment the column?

ERas gravatar imageERas ( 2018-03-17 15:56:36 +0200 )edit

incrementing the column would require another approach; please make a new question for that, with a reference to this question.

librebel gravatar imagelibrebel ( 2018-03-17 17:31:17 +0200 )edit

@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).

Ratslinger gravatar imageRatslinger ( 2018-03-18 03:31:37 +0200 )edit

answered 2018-03-17 04:11:19 +0200

Ratslinger gravatar image

updated 2018-03-17 04:12:06 +0200


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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-03-17 02:39:13 +0200

Seen: 964 times

Last updated: Mar 17 '18