Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines from
line 3 to last line in database. I don't want to say Range("A3", "IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre

try:

Range("A3", "IV3").currentregion.copy
windows(mynewworkbook).sheets([insert "sheetname" or index
here]).range("a3").pastespecial xlpastevalues

this will select the block of contiguous cells
the only issue will be if there is a completely blank row between row 3 and
the last row in the data you want to copy

- voodooJoe

"Alex St-Pierre" wrote in message
...
Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines from
line 3 to last line in database. I don't want to say Range("A3",
"IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre

Thanks, this is exactly what I looked for.
After copying, I want to count the number of row used but it always gives 1
as answer.. If I select the range manualy, nbrow = selection.rows.count gives
the right answer.. Do you have an other function to use ?
Range("A3", "IV3").CurrentRegion.Copy
nbrow = Selection.Rows.Count

--
Alex St-Pierre


"voodooJoe" a écrit :

try:

Range("A3", "IV3").currentregion.copy
windows(mynewworkbook).sheets([insert "sheetname" or index
here]).range("a3").pastespecial xlpastevalues

this will select the block of contiguous cells
the only issue will be if there is a completely blank row between row 3 and
the last row in the data you want to copy

- voodooJoe

"Alex St-Pierre" wrote in message
...
Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines from
line 3 to last line in database. I don't want to say Range("A3",
"IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre

The function Range("A3", "IV3").CurrentRegion.Copy select the line 2 because
the line is not empty. Do you know how to exclude this line in the selection.
Thanks,
Alex St-Pierre

"voodooJoe" a écrit :

try:

Range("A3", "IV3").currentregion.copy
windows(mynewworkbook).sheets([insert "sheetname" or index
here]).range("a3").pastespecial xlpastevalues

this will select the block of contiguous cells
the only issue will be if there is a completely blank row between row 3 and
the last row in the data you want to copy

- voodooJoe

"Alex St-Pierre" wrote in message
...
Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines from
line 3 to last line in database. I don't want to say Range("A3",
"IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre

not sure i fully understand your question, but it apprears you are not
counting the rows in the currentregion.

try

set myRange = Range("A3", "IV3").CurrentRegion
debug.print myRange.rows.count
myrange.copy

Range("A3", "IV3").CurrentRegion.Copy
nbrow = Selection.Rows.Count

"Alex St-Pierre" wrote in message
...
Thanks, this is exactly what I looked for.
After copying, I want to count the number of row used but it always gives
1
as answer.. If I select the range manualy, nbrow = selection.rows.count
gives
the right answer.. Do you have an other function to use ?
Range("A3", "IV3").CurrentRegion.Copy
nbrow = Selection.Rows.Count

--
Alex St-Pierre


"voodooJoe" a écrit :

try:

Range("A3", "IV3").currentregion.copy
windows(mynewworkbook).sheets([insert "sheetname" or index
here]).range("a3").pastespecial xlpastevalues

this will select the block of contiguous cells
the only issue will be if there is a completely blank row between row 3
and
the last row in the data you want to copy

- voodooJoe

"Alex St-Pierre" wrote in
message
...
Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines
from
line 3 to last line in database. I don't want to say Range("A3",
"IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre

does this because it selects the whole current region

try this:

set rng = Range("A3", "IV3").CurrentRegion
set rng = rng.offset(1,0).resize(rowsize:=rng.rows.count-1)
rng.copy

you can combine these into 1 line if you want - i left at 3 so you could
more easily see what i did.
also, you may want to include a test to see if rows 1 and 2 have anything in
them and adjust the offset and resize methods accordingly

-voodooJoe


"Alex St-Pierre" wrote in message
...
The function Range("A3", "IV3").CurrentRegion.Copy select the line 2
because
the line is not empty. Do you know how to exclude this line in the
selection.
Thanks,
Alex St-Pierre

"voodooJoe" a écrit :

try:

Range("A3", "IV3").currentregion.copy
windows(mynewworkbook).sheets([insert "sheetname" or index
here]).range("a3").pastespecial xlpastevalues

this will select the block of contiguous cells
the only issue will be if there is a completely blank row between row 3
and
the last row in the data you want to copy

- voodooJoe

"Alex St-Pierre" wrote in
message
...
Hi,
I have to copy data from one sheet to an other. I use the programmation
below. My problem is that sometimes, the colomn "A" is empty, so, the
"End(x1down) fonction doesn't work. I would like to select all lines
from
line 3 to last line in database. I don't want to say Range("A3",
"IV65536")
because it slow down the process when copying the data.
Does anyone have an idea ?

Range(Range("A3", "IV3"), Range("A3", "IV3").End(xlDown)).Select
Selection.Copy
Windows(mynewworkbook).Activate
Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False--

Thanks,
Alex St-Pierre