Hi,

I created a macro that copies a set of data into a template.

Since the data vary in size, I had to make full column ranges so I
would always cover the cells with data. I also recorded a macro that
creates a formula based on two of those columns then copy/pastes the
formula down to the end of the spreadsheet (eg cell D65536).

After I run the macro to copy the data into the template and perform
the necessary calculations, how can I delete the remaining rows so all
I have are blank cells under the last row of data on my template.

Range("EXP_LF").Select
Selection.Copy
Sheets("Sheet8").Select
Range("N2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("O2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-7]"
Range("O2").Select
Selection.Copy
Range("O3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Thank you

Hi,

Instead of filling down and then delete try this

Range(Range("N2").End(xlDown).Offset(0, 1), Range("O2")).FillDown

You can also refine the macro a little:

Range("EXP_LF").Select
Selection.Copy
Sheets("Sheet8").Select
Range("N2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("O2").FormulaR1C1 = "=RC[-3]-RC[-7]"
Range(Range("N2").End(xlDown).Offset(0, 1), Range("O2")).FillDown
Application.CutCopyMode = False

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"(E-Mail Removed)" wrote:

> Hi,
>
> I created a macro that copies a set of data into a template.
>
> Since the data vary in size, I had to make full column ranges so I
> would always cover the cells with data. I also recorded a macro that
> creates a formula based on two of those columns then copy/pastes the
> formula down to the end of the spreadsheet (eg cell D65536).
>
> After I run the macro to copy the data into the template and perform
> the necessary calculations, how can I delete the remaining rows so all
> I have are blank cells under the last row of data on my template.
>
> Range("EXP_LF").Select
> Selection.Copy
> Sheets("Sheet8").Select
> Range("N2").Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Range("O2").Select
> ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-7]"
> Range("O2").Select
> Selection.Copy
> Range("O3").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
> _
> SkipBlanks:=False, Transpose:=False
> Application.CutCopyMode = False
>
> Thank you
>
>

Thank you very much. This is what I needed to do in the first place.

The other problem I have is that in order to copy the entire ranges
without leaving any data behind, I created (defined) ranges from A2 to
A65536, and B2 to A65536 and so on.

Once I export my data to Excel, and run the macro, is there a way to
only select the cells that have data in them? or do I have to do this
to prevent leaving something behind?

Why not sort the data to remove the blank lines.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"(E-Mail Removed)" wrote:

> Thank you very much. This is what I needed to do in the first place.
>
> The other problem I have is that in order to copy the entire ranges
> without leaving any data behind, I created (defined) ranges from A2 to
> A65536, and B2 to A65536 and so on.
>
> Once I export my data to Excel, and run the macro, is there a way to
> only select the cells that have data in them? or do I have to do this
> to prevent leaving something behind?
>
>