I have a sheet with x numbers of rows that i need to copy to another sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a "1" in
an empty column but only where data exists in the rows. How can this be
done?

Thanks.


dm.

Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a "1" in
an empty column but only where data exists in the rows. How can this be
done?

Thanks.


dm.

Rowan,

Both solutions work as requested but the second one is not exactly what i
was looking for. Here is a better description...

I have data like this...
x y z
x z
x z

Now everywhere y is missing i need to copy y. So i need to copy the contents
of y down to every open cell down to the row that data stops.

any help with this one?
dm.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another
sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically
changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a "1"
in
an empty column but only where data exists in the rows. How can this be
done?

Thanks.


dm.

Hi Daniel

Assuming your data is in the first three columns, starting in Row 2 there
are a few ways to do this. If you simply want to copy the value (or formula)
from cell B2 down to the end of you data then this should do it:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2))

That will replace anything that is already in Column B with whatever comes
from B2. If there are data (or formulas) in other cells in column B that you
do not want to replace then this will fill in only the blank cells:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2)). _
SpecialCells(xlCellTypeBlanks)

Be aware that the second option will only work correctly if you have less
than 8193 non-contiguos ranges in Column B. If you have more than 8192
non-contiguous ranges then all data/formulae in column B will be replaced by
B2.

Hope this helps
Rowan

"Daniel M" wrote:

Rowan,

Both solutions work as requested but the second one is not exactly what i
was looking for. Here is a better description...

I have data like this...
x y z
x z
x z

Now everywhere y is missing i need to copy y. So i need to copy the contents
of y down to every open cell down to the row that data stops.

any help with this one?
dm.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another
sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically
changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a "1"
in
an empty column but only where data exists in the rows. How can this be
done?

Thanks.


dm.

Rowan,

Great! this works better, and now that i have placed with the numbers and
understand it i can use it for different columns. Now the only problem i
have is that one of my cells has the formula =Sheet1!C5 in it and when it
copies the data it changes the formula to C5, C6, C7 etc...Is there a way to
only copy C5?

Thanks.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Hi Daniel

Assuming your data is in the first three columns, starting in Row 2 there
are a few ways to do this. If you simply want to copy the value (or
formula)
from cell B2 down to the end of you data then this should do it:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2))

That will replace anything that is already in Column B with whatever comes
from B2. If there are data (or formulas) in other cells in column B that
you
do not want to replace then this will fill in only the blank cells:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2)). _
SpecialCells(xlCellTypeBlanks)

Be aware that the second option will only work correctly if you have less
than 8193 non-contiguos ranges in Column B. If you have more than 8192
non-contiguous ranges then all data/formulae in column B will be replaced
by
B2.

Hope this helps
Rowan

"Daniel M" wrote:

Rowan,

Both solutions work as requested but the second one is not exactly what i
was looking for. Here is a better description...

I have data like this...
x y z
x z
x z

Now everywhere y is missing i need to copy y. So i need to copy the
contents
of y down to every open cell down to the row that data stops.

any help with this one?
dm.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another
sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically
changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a
"1"
in
an empty column but only where data exists in the rows. How can this
be
done?

Thanks.


dm.

Change the original formula to =Sheet1!$C$5.

Regards
Rowan

"Daniel M" wrote:

Rowan,

Great! this works better, and now that i have placed with the numbers and
understand it i can use it for different columns. Now the only problem i
have is that one of my cells has the formula =Sheet1!C5 in it and when it
copies the data it changes the formula to C5, C6, C7 etc...Is there a way to
only copy C5?

Thanks.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Hi Daniel

Assuming your data is in the first three columns, starting in Row 2 there
are a few ways to do this. If you simply want to copy the value (or
formula)
from cell B2 down to the end of you data then this should do it:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2))

That will replace anything that is already in Column B with whatever comes
from B2. If there are data (or formulas) in other cells in column B that
you
do not want to replace then this will fill in only the blank cells:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2").Copy Destination:=Range(Cells(2, 2), Cells(eRow, 2)). _
SpecialCells(xlCellTypeBlanks)

Be aware that the second option will only work correctly if you have less
than 8193 non-contiguos ranges in Column B. If you have more than 8192
non-contiguous ranges then all data/formulae in column B will be replaced
by
B2.

Hope this helps
Rowan

"Daniel M" wrote:

Rowan,

Both solutions work as requested but the second one is not exactly what i
was looking for. Here is a better description...

I have data like this...
x y z
x z
x z

Now everywhere y is missing i need to copy y. So i need to copy the
contents
of y down to every open cell down to the row that data stops.

any help with this one?
dm.

"Rowan" <rowanzsa at hotmailNOSPAM dot com wrote in message
...
Assuming your data starts in Cell A1 then Answer1:

Dim eRow As Long
eRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & eRow).EntireRow.Copy Destination:=Sheets(2).Range("A1")

Answer2:

Dim eRow As Long
Dim eCol As Integer
eRow = Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Range(Cells(1, eCol), Cells(eRow, eCol)).Value = 1

Hope this helps
Rowan

"Daniel M" wrote:

I have a sheet with x numbers of rows that i need to copy to another
sheet.
If it was row 1 - 5 each time that woudl be fine but it dynamically
changes.
how do i select only the rows that have data?

Second question. I have data in x numbers of rows and i want to add a
"1"
in
an empty column but only where data exists in the rows. How can this
be
done?

Thanks.


dm.