MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy some range only?

  Asked By: Lourdes    Date: Aug 28    Category: MS Office    Views: 860

I have a range like A2:N22 , how can I copy the available range (with
data only) within this maximum range with code??



10 Answers Found

Answer #1    Answered By: Julia Silva     Answered On: Aug 28

Have you tried recording a macro doing this to see what the code  looks

Answer #2    Answered By: Grant Jones     Answered On: Aug 28

if that is going to be a set range  you just have to select that range.. and when
you go to paste it.. use the pastespecial data only option... if you want more
i'll play a little and post some code  for it.. if it's a set range you can
also name that range to make it easier to select and copy... if however that
is only a maximum  and you want to copy  only available data in that range
dynamically.. then you'd have to use conditional formatting and get the last row
used for the select statement..

Answer #3    Answered By: Phailin Jainukul     Answered On: Aug 28

Sub Macro1()
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

End Sub

This is what the code  looks like , the range  ( A2:N22 ) is always fixed
but sometimes the data only available ,say at A2 : N14 , so I would like
to copy  only this only without carrying the whole range ( A2:N22 )

Answer #4    Answered By: Bonita Garcia     Answered On: Aug 28

then i would do this...

sub macro1()
dim irow as integer
irow = Range("N22").end(xlup).row

range("A2:N" & irow).select
Selection.pastespecial paste:=xlpasteall, operation:=xlnone, Skipblanks:= _
False, Transpose:=False
end sub

Answer #5    Answered By: Elmer Young     Answered On: Aug 28

that works if you change:

irow = Range("N22").end(xlup).row


irow = Range("N23").end(xlup).row

If you don't make that change, then you will copy  only the first
line (row 2) when there is data in row 22.

Answer #6    Answered By: Glenda Frazier     Answered On: Aug 28

that's what i get for copying.. i new that.. just didn't change the
value... good catch.. thanks

Answer #7    Answered By: Vilmos Fischer     Answered On: Aug 28

No problem. I learned something from your code  (I wasn't familiar
with ".end(xlup)"), so thanks for listing it.

Answer #8    Answered By: Jakson Williams     Answered On: Aug 28

if you play with it you'll see theres xlup, xldown, right and left as well...i
think there is more options but can't think of them right now... and that's how
i found out about it.. someone else posted it... ;) share the wealth never
know when you'll need to borrow it

Answer #9    Answered By: Abney Martin     Answered On: Aug 28

I don't find any pastespecial with data only option ...? However the
idea is to copy  only available data range  dynamically within the
specified bigger range

Answer #10    Answered By: Fannie Gonzales     Answered On: Aug 28

Wow this is good , manage to get it , but somehow because the range  that
I want to copy  got a link and formula that being hide , it still carry
the whole range , any ideas how to avoid this ?

Didn't find what you were looking for? Find more on Copy some range only? Or get search suggestion and latest updates.