Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
....


--
Mathieu

Hi,

It's either a helper column or a macro. using a helper column enter Montreal
in B1 then the formula below in B2 and drag down as far as required. If you
need a macro post back.

=IF(A2="",B1,A2)

Paste the new column over the original with paste special - values and then
delete the helper column
Mike

"Mathieu" wrote:

Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu

Hi,

It's need to be a macro. They can be hundreds of information to paste. I am
using town from Canada in my exemple. Let's assume we will use EVERY cities
in Canada. This is why a macro is required. I considered myself a
beginner-novice with VBA, I understand the basis for the moment but this one
is completely eluding me.

Thanks
--
Mathieu


"Mike H" wrote:

Hi,

It's either a helper column or a macro. using a helper column enter Montreal
in B1 then the formula below in B2 and drag down as far as required. If you
need a macro post back.

=IF(A2="",B1,A2)

Paste the new column over the original with paste special - values and then
delete the helper column
Mike

"Mathieu" wrote:

Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu

I anticipated that and have one written. this fills Column A as fra as there
are dat in Column B

Right click your sheet tab, view code and paste it in and run it.

Sub Canadian_Fill()
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow - 1)
For Each c In MyRange
If c.Offset(1).Value = "" Then
c.Offset(1).Value = c.Value
End If
Next
End Sub

Mike

"Mathieu" wrote:

Hi,

It's need to be a macro. They can be hundreds of information to paste. I am
using town from Canada in my exemple. Let's assume we will use EVERY cities
in Canada. This is why a macro is required. I considered myself a
beginner-novice with VBA, I understand the basis for the moment but this one
is completely eluding me.

Thanks
--
Mathieu


"Mike H" wrote:

Hi,

It's either a helper column or a macro. using a helper column enter Montreal
in B1 then the formula below in B2 and drag down as far as required. If you
need a macro post back.

=IF(A2="",B1,A2)

Paste the new column over the original with paste special - values and then
delete the helper column
Mike

"Mathieu" wrote:

Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu

Thank you, it is working great! I will now try to understand the difference
with yours and Per Jessen. Both are working Great!

Thanks again!
--
Mathieu


"Mike H" wrote:

I anticipated that and have one written. this fills Column A as fra as there
are dat in Column B

Right click your sheet tab, view code and paste it in and run it.

Sub Canadian_Fill()
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow - 1)
For Each c In MyRange
If c.Offset(1).Value = "" Then
c.Offset(1).Value = c.Value
End If
Next
End Sub

Mike

"Mathieu" wrote:

Hi,

It's need to be a macro. They can be hundreds of information to paste. I am
using town from Canada in my exemple. Let's assume we will use EVERY cities
in Canada. This is why a macro is required. I considered myself a
beginner-novice with VBA, I understand the basis for the moment but this one
is completely eluding me.

Thanks
--
Mathieu


"Mike H" wrote:

Hi,

It's either a helper column or a macro. using a helper column enter Montreal
in B1 then the formula below in B2 and drag down as far as required. If you
need a macro post back.

=IF(A2="",B1,A2)

Paste the new column over the original with paste special - values and then
delete the helper column
Mike

"Mathieu" wrote:

Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would like
to copy Montreal and paste it up to cell A4 then start the same process with
Quebec. The number of time that Montreal (in the example could be paste can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu

Hi

Try this:

Sub FillIn()
MyString = Range("A1").Value
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow
If Cells(r, 1) = "" Then
Cells(r, 1) = MyString
Else
MyString = Cells(r, 1).Value
End If
Next
End Sub

Regards,
Per

"Mathieu" skrev i meddelelsen
...
Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I
need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would
like
to copy Montreal and paste it up to cell A4 then start the same process
with
Quebec. The number of time that Montreal (in the example could be paste
can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu

Thank you, it is working great! I will now try to understand the difference
with yours and Mike H. Both are working Great!

Thanks again!
--
Mathieu


"Per Jessen" wrote:

Hi

Try this:

Sub FillIn()
MyString = Range("A1").Value
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To LastRow
If Cells(r, 1) = "" Then
Cells(r, 1) = MyString
Else
MyString = Cells(r, 1).Value
End If
Next
End Sub

Regards,
Per

"Mathieu" skrev i meddelelsen
...
Hi, I have an automated report (which we cannot change ;) ) that is not
applying data constantly, event if they are in the right order. What I
need
to do is to Copy the first line and paste it until the next value. I am
really stuck on this one. Thanks for your help. On the exemple. I would
like
to copy Montreal and paste it up to cell A4 then start the same process
with
Quebec. The number of time that Montreal (in the example could be paste
can
vary a lot from time to time and all the other fields below have the same
problem.


A B C
1 Montreal
2
3
4
5 Quebec
6
7
8
...


--
Mathieu