Using .Row help please

[Simon]

Each month I add 2 rows from the bottom of the previous month.

To do this I would like some assistance please.

I thought it would encompass

Range [A36636].Select
Selection.End(xlUp).Offset(-1, 0).Row

'then somehow select the bottom two used rows and copy them directly
underneath the copied rows

Thanks for your help. Unfortunately I have ye tto learn much about
the Row property.

Regards,

Si

 

[Dave Peterson]

You just want to copy the last 2 rows to the bottom of the range?

Dim RngToCopy as range
dim DestCell as range

with activesheet
set rngtocopy = .cells(.rows.count,"A").end(xlup).offset(-1,0)
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

rngtocopy.resize(2,1).entirerow.copy _
destination:=destcell

Each month I add 2 rows from the bottom of the previous month.

To do this I would like some assistance please.

I thought it would encompass

Range [A36636].Select
Selection.End(xlUp).Offset(-1, 0).Row

'then somehow select the bottom two used rows and copy them directly
underneath the copied rows

Thanks for your help. Unfortunately I have ye tto learn much about
the Row property.

Regards,

Si

 

[Don Guillett]

I would prefer to see an example of before and after desires but maybe this
helps

preferred
myrow=cells(rows.count,"a").end(xlup).row-1

or
myrow=range("a65536").end(xlup).row-1

 

[J Sedoff comRemove>]

This should work. It finds the last occupied cell in Column A, copies that
row and the row above it, then pastes it directly under the last occupied row.

Range("A65536").End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Offset(1, 0).Row).Copy
ActiveCell.Offset(2, 0).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Offset(1, 0).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Hope this helps, Jim

 

[Simon]

You just want to copy the last 2 rows to the bottom of the range?

Dim RngToCopy as range
dim DestCell as range

with activesheet
  set rngtocopy = .cells(.rows.count,"A").end(xlup).offset(-1,0)
  set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

rngtocopy.resize(2,1).entirerow.copy _
  destination:=destcell

Each month I add 2 rows from the bottom of the previous month.

To do this I would like some assistance please.

I thought it would encompass

Range [A36636].Select
Selection.End(xlUp).Offset(-1, 0).Row

'then somehow select the bottom two used rows and copy them directly
underneath the copied rows

Thanks for your help.  Unfortunately I have ye tto learn much about
the Row property.

Si

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave,

It works, you are a genius!

Does the A in cells(.rows.count,"A").end(xlup).offset(-1,0) refer to
column A?

What does Resize(2, 1) do? thanks.

 

[Simon]

This should work.  It finds the last occupied cell in Column A, copies that
row and the row above it, then pastes it directly under the last occupiedrow.

    Range("A65536").End(xlUp).Select
    ActiveCell.Offset(-1, 0).Select
    Rows(ActiveCell.Row & ":" & ActiveCell.Offset(1, 0).Row).Copy
    ActiveCell.Offset(2, 0).Select
    Rows(ActiveCell.Row & ":" & ActiveCell.Offset(1, 0).Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Hope this helps, Jim

Hi Jim,

In fact your method is better (sorry guys), I didn't notice at first
but for the two rows in question (top row has totals and row beneath
has percentages, however in row A, only totals are used, therefore
only the top row is copied (or that row and the row above it).

Yet column F also has both therefore column F is the better reference
to use (F65536), therefore your method has the flexibiltiy to easier
amend, rather than having to amend the offsets.

(Are the offset ranges referred to as indexes?)

There is no fear of column F losing its "position" of not having data
in both rows, as it is always copied from above.

 

[Dave Peterson]

Yep. That "A" means column A. When you use cells(), you can either specify a
string or a number for that column argument.

cells(1,26)
and
cells(1,"Z")
both refer to the same cell.

And .resize(x,y) says to resize whatever the starting range is (in this case
that single cell) and make it x rows by y columns.

So
rngtocopy.resize(2,1).entirerow.copy _
says to make it 2 rows by 1 column, but then copy the .entirerow.

 

[J Sedoff comRemove>]

The numbers in the offset parenthesis are indeed called indexes. They are in
the order (as much of the range-type commands are) of Row Index, Column Index.

(Do you understand what it is doing? I don't mind explaining it, if you
want.)

I hope this works for you, Jim