Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank (Cells/Rows) in Excel-VBA
Help I have a macro that runs multiple formulas. However, when the formulas
are finished I am left with about 64000 blank lines. I am unable to insert a row on the worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank Cell using CTRL-END). Here is a same of some of the formulas I am running ( the macro is very large). ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27" ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28" ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29" ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30" ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31" ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32" ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33" ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34" ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35" ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36" ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37" 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO BOTTOM OF SHEET Range("AA1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-17]"""",IF(RC110,OFFSET(RC[-16],0,0),""""),"""")" 'Defines a variable called anchor cell Application.Goto Reference:="TrunkFormulaI", Scroll:=False Selection.Copy AnchorCell = ActiveCell.Offset(1, 0).Address ActiveCell.Offset(0, -1).Select ActiveCell.End(xlDown).Select EndCell = ActiveCell.Offset(0, 1).Address Range(AnchorCell, EndCell).Select ActiveSheet.Paste Application.CutCopyMode = False Application.Goto Reference:="TrunkFormulaI", Scroll:=False Range("G:G").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False 'RUN-TIME ERROR 1004 Range("A1").EntireRow.Insert Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank (Cells/Rows) in Excel-VBA
You can try this to get rid of the blank rows. Pick a column that would
normally have had data all the way down before the macro ran. For demo purposes let's use col A. Sub delBlnkRws() lastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("$A$1:$A" & lastRow).SpecialCells(xlCellTypeBlanks).EntireRow. Delete End Sub Try it on a copy first to make sure it is what you want. "VexedFist" wrote: Help I have a macro that runs multiple formulas. However, when the formulas are finished I am left with about 64000 blank lines. I am unable to insert a row on the worksheets as I get a Run-Time Error 1004 (Try to locate the last Nonblank Cell using CTRL-END). Here is a same of some of the formulas I am running ( the macro is very large). ActiveWorkbook.Names.Add Name:="TrunkFormulaI", RefersToR1C1:="=Trunks!R1C27" ActiveWorkbook.Names.Add Name:="TrunkFormulaJ", RefersToR1C1:="=Trunks!R1C28" ActiveWorkbook.Names.Add Name:="TrunkFormulaK", RefersToR1C1:="=Trunks!R1C29" ActiveWorkbook.Names.Add Name:="TrunkFormulaL", RefersToR1C1:="=Trunks!R1C30" ActiveWorkbook.Names.Add Name:="TrunkFormulaM", RefersToR1C1:="=Trunks!R1C31" ActiveWorkbook.Names.Add Name:="TrunkFormulaN", RefersToR1C1:="=Trunks!R1C32" ActiveWorkbook.Names.Add Name:="TrunkFormulaO", RefersToR1C1:="=Trunks!R1C33" ActiveWorkbook.Names.Add Name:="TrunkFormulaP", RefersToR1C1:="=Trunks!R1C34" ActiveWorkbook.Names.Add Name:="TrunkFormulaQ", RefersToR1C1:="=Trunks!R1C35" ActiveWorkbook.Names.Add Name:="TrunkFormulaR", RefersToR1C1:="=Trunks!R1C36" ActiveWorkbook.Names.Add Name:="TrunkFormulaS", RefersToR1C1:="=Trunks!R1C37" 'INSERT ANCHOR CELL FORMULA FOR THIS SECTION TO COPY AND PASTE FORMULAS TO BOTTOM OF SHEET Range("AA1").Select ActiveCell.FormulaR1C1 = "=IF(RC[-17]"""",IF(RC110,OFFSET(RC[-16],0,0),""""),"""")" 'Defines a variable called anchor cell Application.Goto Reference:="TrunkFormulaI", Scroll:=False Selection.Copy AnchorCell = ActiveCell.Offset(1, 0).Address ActiveCell.Offset(0, -1).Select ActiveCell.End(xlDown).Select EndCell = ActiveCell.Offset(0, 1).Address Range(AnchorCell, EndCell).Select ActiveSheet.Paste Application.CutCopyMode = False Application.Goto Reference:="TrunkFormulaI", Scroll:=False Range("G:G").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False 'RUN-TIME ERROR 1004 Range("A1").EntireRow.Insert Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reduce excel file size by deleting blank rows and columns?? | Excel Discussion (Misc queries) | |||
deleting rows with blank cells after a specified column? | Excel Discussion (Misc queries) | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
Deleting rows with blank cells | Excel Programming | |||
Deleting rows with blank cells | Excel Worksheet Functions |