VBA to reduce selection by 1 row

[KDavidP1987]

Hello,

I am trying to find a way to reduce a selection of cells by 1 row, from the bottom, using VBA. The same way you could on a keyboard by holding shift and the up key. This action is performed repeatedly across several tables, and is needed to remove the totals row at the bottom after selecting down.

So far, my VBA looks like this, but the command I tried (in bold below) isn't working

    Range("B8:D8").Select
    Range(Selection, Selection.End(xlDown)).Select
[B]    Selection.Resize (Selection.Rows.Count - 1)[/B]
    Selection.Copy

I feel like a dunce, because I haven't been able to find a simple solution through google so far.

Any ideas?

Sincerely,
Kristopher

 

[mumps]

If the totals row is the very last row, try this:

Sub delRow()
    Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete
End Sub

 

[KDavidP1987]

If the totals row is the very last row, try this:

Sub delRow()
    Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Delete
End Sub

Unfortunately, the data it's being copied off a pivot table. The final row can be selected, but not deleted from the data sets.

However, you did give me an idea... I can just remove the grand totals row from the pivot table settings, practically the same thing. Didn't think of that before, thank you so much!!

Out of curiosity though, is there no way to deselect 1 row from a selection using VBA??

Sincerely,
Kristopher

 

[mumps]

I'm glad it worked out.

Try:

Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select

 

[Rick Rothstein]

Try:

Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select

You do not need to specify the row or the column argument if it is not changing (Resize only changes what you tell it to change); hence, this will also work...

Selection.Resize(Selection.Rows.Count - 1).Select

 

[mumps]

Thanks for the update, Rick.