Deleting Blank lines with VBA

G

Guest

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(RC11>0,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 Appears HERE

Range("A1").EntireRow.Insert


Any help would be appreciated.
 
D

Dave Peterson

Can you pick out a column that is always used when that row is used? If you can
then maybe you can just fill the formulas through that last row.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
with .range("AA1:aa" & lastrow)
.formular1c1 _
"=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
.value = .value
end with
end with



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(RC11>0,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 Appears HERE

Range("A1").EntireRow.Insert

Any help would be appreciated.
Click to expand...
 
G

Guest

Dave,

When I run this I get:

Run-Time Error 438

Object doens't support this Property or Method??



Dave Peterson said:
Can you pick out a column that is always used when that row is used? If you can
then maybe you can just fill the formulas through that last row.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
with .range("AA1:aa" & lastrow)
.formular1c1 _
"=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
.value = .value
end with
end with



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(RC11>0,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 Appears HERE

Range("A1").EntireRow.Insert

Any help would be appreciated.
Click to expand...
Click to expand...
 
G

Guest

Dave,

When I run this i get:

Run-Time Error 438

Object doesn't support this Property or Method!!



Dave Peterson said:
Can you pick out a column that is always used when that row is used? If you can
then maybe you can just fill the formulas through that last row.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
with .range("AA1:aa" & lastrow)
.formular1c1 _
"=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
.value = .value
end with
end with



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(RC11>0,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 Appears HERE

Range("A1").EntireRow.Insert

Any help would be appreciated.
Click to expand...
Click to expand...
 
D

Dave Peterson

There was a typo. I dropped an equal sign:

.formular1c1 _
= "=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"


Dave,

When I run this i get:

Run-Time Error 438

Object doesn't support this Property or Method!!

Dave Peterson said:
Can you pick out a column that is always used when that row is used? If you can
then maybe you can just fill the formulas through that last row.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row 'I used column A
with .range("AA1:aa" & lastrow)
.formular1c1 _
"=IF(RC[-17]>"""",IF(RC11>0,OFFSET(RC[-16],0,0),""""),"""")"
.value = .value
end with
end with



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(RC11>0,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 Appears HERE

Range("A1").EntireRow.Insert

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting blank (Cells/Rows) in Excel-VBA 1
Automatically generated email from Excel 2
Excel loops and If blocks 4
Adding User Dialog to Macro 2
"Subscript Out of Range" Error Question 2
Macro Reference 2
Runtime Error '-2147417848 (80010108)' 3
VBA Pivot Table Error 1

Top