Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting extra rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting extra rows
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. " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting extra rows
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting extra rows
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. " 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting extra rows in Excel 07 | Excel Discussion (Misc queries) | |||
Deleting Extra Page | Excel Discussion (Misc queries) | |||
How to compress an Excel file once deleting extra worksheets | Excel Discussion (Misc queries) | |||
limiting or deleting extra characters in a cell | Excel Discussion (Misc queries) | |||
deleting extra spaces in a string | Excel Programming |