How to move up one visible row?

Question

I'm trying to insert a row above, then go up one to the next visible row in a filtered spreadsheet. Because I want to apply the action to visible rows (which can be spaced randomly), this line of code isn't sufficient:

ActiveCell.Offset(-8, 0)

As I understand it, this tells Excel to move up eight rows (because the cell set I used to record the macro happened to be spaced eight cells apart), not to the next visible row under present filter conditions.

Excerpt of spreadsheet showing that visible data rows are randomly spaced

in this case 6 rows apart but it could be any number.

The macro code:

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-8, 0).Rows("1:1").EntireRow.Select

I'm looking to replace the last line of code "go up eight rows" with "go up to next visible row, given current data filters".

The filter shows rows where Column R = 0, but I'd prefer a method where I don't incorporate that instruction into the VBA code and just look at the visible data instead of the whole spreadsheet.

*"doesn't work"* is a pretty useless error description. And *"Any ideas?"* is a pretty random question. Please see [ask] and [mcve] to give a good example and ask a good question. I highly recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) because this is the main reason you are struggling with the solution to this. • Note that sceenshots and/or example data makes it much easier to understand what's going on. — Pᴇʜ, Feb 04 '21 at 08:18

score 0 · Answer 1 · answered Feb 04 '21 at 08:34

0

You should check or Row is hidden:

If Rows(15).Hidden then 'we are checking is Row 15 Hidden
    'row is Hidden
Else
    'row is Visible
End If

answered Feb 04 '21 at 08:34

bankeris

181
10

So combine the above with an activecell.offset at -1, and let it cycle through the entire spreadsheet that way? I can see how that might work, not fluent in the necessary code language but I'll give it a shot and see what I come up with. – David Stosser Feb 04 '21 at 08:36
This code appears to work, although it is noticeably slower than the sort of code that skips to the next visible row instead of testing each individually: `Sub Insertrowabove()If ActiveCell.Rows("1:1").Hidden Then ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select Else ActiveCell.Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select End If End Sub` (How do I get code to display lines neatly in the comments?) – David Stosser Feb 04 '21 at 10:06
This idea that @David Stosser has is definitely easier, to keep going to ActiveCell.Offset(-1, 0) until you find one which is visible! I recommend using it and I recommend you David to add it as an answer. – Timothy Alexis Vass Feb 04 '21 at 10:13
@TimothyAlexisVass, I've copied the above code and posted below as an alternative answer with some commentary. – David Stosser Feb 04 '21 at 10:53

score 0 · Accepted Answer · answered Feb 04 '21 at 10:01

So as I understand it, you want to insert a row above the current row, then press Ctrl-up so that it skips to the last row which isn't blank? Try this:

r = Selection.Row: c = Selection.Column
Rows(LTrim(Str(r)) + ":" + LTrim(Str(r))).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
destrow = Range("A" & r).End(xlUp).Row
Cells(destrow, c).Select

Excuse my coding style kids, I grew up on GW-BASIC and my only stylistic concession to the 21st century is that I don't type in all caps.

That does exactly what I need, and much faster than the `If`-based function below :) — David Stosser, Feb 04 '21 at 10:17

score 0 · Answer 3 · answered Feb 04 '21 at 10:52

Per Timothy's suggestion, this is the code that I came up with based on bankeris's work:

    'row is Hidden
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    'move up one cell
Else
    'row is Visible
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    'insert row above then move up one.
End If

Although I found Michael Angelico's post above to be a faster solution for my specific requirements, this is a more generalised option.

How to move up one visible row?

3 Answers3