Ask Your Question

Search for a sequence of numbers from bottom of spreadsheet...

asked 2014-06-29 03:02:19 +0100

userx gravatar image

updated 2015-09-03 00:23:10 +0100

Alex Kemp gravatar image

This question may seem odd and is perhaps downright whimsical. I have searched for macros that could serve my purpose but have not found anything that would search in reverse numerical sequence from the bottom of a spreadsheet. I am using LibraOffice version 4 on Centos 6.5. My spreadsheet has 25 columns and many rows. The cells of each row/column are populated with a number from 1 to 50 and arranged in no particular order. A new row of numbers are frequently added at the bottom of the sheet. I need to write a macro that will:

  1. Find the last row containing data.
  2. For each column and value in a cell in the last row, search the previous rows for the next lower value until the entire sequence of numbers (1 through 50 and a max one number decremented per row) have been found. For example, if a cells value on the first column in the last row = 5 then search backwards row by row for the numbers 4,3,2,1,50,49.48...10,9,8,7 and finally 6. Perform this on each column of each row.
  3. Keep track of the number of rows passed during the search and divide Nrows by 50 for the average rows a number decrements.
  4. Rerun macro for each new row that is added.

I would really like to read some suggestions or example code on how this can be done Seems that it should be a for loop in reverse?

edit retag flag offensive close merge delete


The task is not clear. If you do not have exact 50 rows, then you will have gaps or double numbers. What to do? Are all rows filled, or do empty cells exist? Why you need to add the new rows at the end? Add it at the beginning and you can use normal search functions. What do you want to do with the resulting average? Why use a macro and not a separate sheet for calculation?

Regina gravatar imageRegina ( 2014-06-29 21:30:02 +0100 )edit

I think you're quickly approaching the threshold where it's just simpler to use bash + grep + awk (or python if you prefer).

Avio gravatar imageAvio ( 2017-01-20 12:40:44 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-02-17 17:05:08 +0100

als50 gravatar image

updated 2019-02-17 17:07:37 +0100

This is probably OBE but if you are playing around and would still like something to get started...

get the last row and column:

Dim oCell As Object, oCursor As Object
   Dim endCol As Long, endRow As Long
  oCell = oSheet.getCellByPosition(0,0)     ' set to the first cell in the sheet
  oCursor = oSheet.createCursorByRange(oCell)   ' create a cursor
  oCursor.GoToEndOfUsedArea(True)           ' move cursor to the end of the data
  endCol = oCursor.RangeAddress.EndColumn       ' assign the index of the last column
  endRow = oCursor.RangeAddress.EndRow      ' assign the index of the last row

get the value of a cell:

oSheet.getCellByPosition(column, row) ' column & row are Long

the rest is just counters and looping. A for loop decrementing from the last row will work.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-06-29 03:02:19 +0100

Seen: 233 times

Last updated: Feb 17