i have a sheet populated by data from a database query, parts of the rows of
data are coppied into a different sheet with a totals row at the bottom. The
problem is, the data i am using can range from 10 to hundreds of rows long,
but the totals row always needs to be at the bottom of the data (next to it).

what i really want to do is create a macro that inserts a row underneith a
row which contains values (specifically values as all rows contain formulas)
and deletes rows if the row above is empty.

i have a macro that adds a new row and fills down the formulas only from
the line above, but getting it to run to thte above specifications is causing
a problem.

Any ideas or help would be much appreiated

regards

Patrick

Inserting rows and deleting rows at the same time becomes a very difficult
task to get your loop counters correct. I recommend that you do it in two
passes. First Delete rows then add rows.

When you add rows you usually have to have two counters. One to keep track
of the number of times to loop through the code. The second to count the row
number.

When deleting you have to increment your loop counter only when you are not
deleting a row. Here is a simple version of the delete

Sub test()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

RowCount = 1
For LoopCount = 1 To LastRow
LastColumn = Cells(RowCount, Columns.Count).Column
found = False
For ColumnCount = 1 To LastColumn
If Not IsEmpty(Cells(RowCount, ColumnCount)) Then
found = True
Exit For
End If
Next ColumnCount
If found = False Then
Rows(RowCount).Delete
Else
RowCount = RowCount + 1
End If

Next LoopCount

End Sub

"Patrick Bateman" wrote:

> i have a sheet populated by data from a database query, parts of the rows of
> data are coppied into a different sheet with a totals row at the bottom. The
> problem is, the data i am using can range from 10 to hundreds of rows long,
> but the totals row always needs to be at the bottom of the data (next to it).
>
> what i really want to do is create a macro that inserts a row underneith a
> row which contains values (specifically values as all rows contain formulas)
> and deletes rows if the row above is empty.
>
> i have a macro that adds a new row and fills down the formulas only from
> the line above, but getting it to run to thte above specifications is causing
> a problem.
>
> Any ideas or help would be much appreiated
>
> regards
>
> Patrick

Fair advice, methinks, Joel. Here's a different approach.
I confess I had trouble following what the OP's actual desire
is.

Sub test2()
' delete entire rows if row range is blank

Dim iLastRow, iLastCol As Long
Dim iRow As Long

iLastRow = Cells(Rows.Count, "C").End(xlUp).row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

For iRow = iLastRow To 1 Step -1
With Range(Cells(iRow, "A"), Cells(iRow, iLastCol)).Activate
On Error Resume Next
If Not Selection.Find(What:="?").Activate Then
Rows(iRow).Delete
iLastRow = iLastRow - 1
End If
End With
Next 'iRow

' use iLastRow to create the Totals row now as desired
Debug.Print "iLastRow Is Now " & iLastRow

End Sub

=dman=

============================
In <2074A011-6C36-4D54-8DAB-(E-Mail Removed)>, Joel
<(E-Mail Removed)> spake thusly:

> Inserting rows and deleting rows at the same time becomes a very
> difficult task to get your loop counters correct. I recommend
> that you do it in two passes. First Delete rows then add rows.
>
> When you add rows you usually have to have two counters. One to
> keep track of the number of times to loop through the code. The
> second to count the row number.
>
> When deleting you have to increment your loop counter only when
> you are not deleting a row. Here is a simple version of the
> delete
>
> Sub test()
>
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
>
> RowCount = 1
> For LoopCount = 1 To LastRow
> LastColumn = Cells(RowCount, Columns.Count).Column
> found = False
> For ColumnCount = 1 To LastColumn
> If Not IsEmpty(Cells(RowCount, ColumnCount)) Then
> found = True
> Exit For
> End If
> Next ColumnCount
> If found = False Then
> Rows(RowCount).Delete
> Else
> RowCount = RowCount + 1
> End If
>
> Next LoopCount
>
> End Sub
> "Patrick Bateman" wrote:
>
> > i have a sheet populated by data from a database query, parts
> > of the rows of data are coppied into a different sheet with a
> > totals row at the bottom. The problem is, the data i am using
> > can range from 10 to hundreds of rows long, but the totals row
> > always needs to be at the bottom of the data (next to it).
> >
> > what i really want to do is create a macro that inserts a row
> > underneith a row which contains values (specifically values as
> > all rows contain formulas) and deletes rows if the row above is
> > empty.
> >
> > i have a macro that adds a new row and fills down the
> > formulas only from the line above, but getting it to run to
> > thte above specifications is causing a problem.
> >
> > Any ideas or help would be much appreiated
> >
> > regards
> >
> > Patrick

Regarding the desire to delete any row in a range which contains only
blank cells: the received wisdom has been to loop.

I tried to think of an efficient way. I didn't like looping
through all rows. I decided we could just look for areas that are
blank and delete them without looping. Then all that might be left
would be multi-row areas that turn out not to be empty in all cells
of the row's range. We could simply loop through those few and
decide. It took me some work, but I have code now that works. I
will put it up on a web page later this weekend. (The URL is in a
comment below, but so far there's nothing there.)

Improvements gladly solicited.

=dman=

'----------------------

Option Explicit
Sub DelEmptyRows()

' If all cells in a row within range are empty, delete row
' Ver. 1.0 by Dallman Ross, 8 Sep 2007; use freely
' Latest version: http://heliotropos.com/xl/code/samples.html
'
' Goal was to reduce looping to a minimum; _
hence, we only loop through multi-row "candidate" areas _
as needed

Dim iLastRow, iLastCol As Long
Dim startCell, myIsect, myRg As Range
Dim area As Range
Dim r, rCount As Long


Set startCell = Range("A1") ' change as desired

iLastRow = Cells(Rows.Count, startCell).End(xlUp).row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

On Error Resume Next
Set myRg = Range(startCell, Cells(iLastRow, iLastCol))

Set myIsect = Intersect(myRg, _
Columns(startCell).SpecialCells(xlCellTypeBlanks)). _
EntireRow.Areas ' all range rows with blanks in 1st col.


For Each area In myIsect
area.Activate
'Debug.Print area.row

If Selection.Find("?") = False Then
'// There were only blank cells in the range row(s)
Selection.EntireRow.Delete

Else
'// Loop through remaining multirow "candidate" ranges _
only as needed

'Debug.Print area.Rows.Count, area.row
For r = area.row To area.row + area.Rows.Count - 1
Intersect(area, Rows(r)).Select

If Selection.Find("?") = False _
Then Selection.EntireRow.Delete
Next 'r
End If
Next 'area
End Sub

'----------------------

====================================
In <fbrg3l$iuj$(E-Mail Removed)>, Dallman Ross <dman@localhost.>
spake thusly:

>
> Fair advice, methinks, Joel. Here's a different approach.
> I confess I had trouble following what the OP's actual desire
> is.
>
> Sub test2()
> ' delete entire rows if row range is blank
>
> Dim iLastRow, iLastCol As Long
> Dim iRow As Long
>
> iLastRow = Cells(Rows.Count, "C").End(xlUp).row
> iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
>
> For iRow = iLastRow To 1 Step -1
> With Range(Cells(iRow, "A"), Cells(iRow, iLastCol)).Activate
> On Error Resume Next
> If Not Selection.Find(What:="?").Activate Then
> Rows(iRow).Delete
> iLastRow = iLastRow - 1
> End If
> End With
> Next 'iRow
>
> ' use iLastRow to create the Totals row now as desired
> Debug.Print "iLastRow Is Now " & iLastRow
>
> End Sub
>
> =dman=
>
> ============================
> In <2074A011-6C36-4D54-8DAB-(E-Mail Removed)>, Joel
> <(E-Mail Removed)> spake thusly:
>
> > Inserting rows and deleting rows at the same time becomes a very
> > difficult task to get your loop counters correct. I recommend
> > that you do it in two passes. First Delete rows then add rows.
> >
> > When you add rows you usually have to have two counters. One to
> > keep track of the number of times to loop through the code. The
> > second to count the row number.
> >
> > When deleting you have to increment your loop counter only when
> > you are not deleting a row. Here is a simple version of the
> > delete
> >
> > Sub test()
> >
> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> >
> > RowCount = 1
> > For LoopCount = 1 To LastRow
> > LastColumn = Cells(RowCount, Columns.Count).Column
> > found = False
> > For ColumnCount = 1 To LastColumn
> > If Not IsEmpty(Cells(RowCount, ColumnCount)) Then
> > found = True
> > Exit For
> > End If
> > Next ColumnCount
> > If found = False Then
> > Rows(RowCount).Delete
> > Else
> > RowCount = RowCount + 1
> > End If
> >
> > Next LoopCount
> >
> > End Sub
> > "Patrick Bateman" wrote:
> >
> > > i have a sheet populated by data from a database query, parts
> > > of the rows of data are coppied into a different sheet with a
> > > totals row at the bottom. The problem is, the data i am using
> > > can range from 10 to hundreds of rows long, but the totals row
> > > always needs to be at the bottom of the data (next to it).
> > >
> > > what i really want to do is create a macro that inserts a row
> > > underneith a row which contains values (specifically values as
> > > all rows contain formulas) and deletes rows if the row above is
> > > empty.
> > >
> > > i have a macro that adds a new row and fills down the
> > > formulas only from the line above, but getting it to run to
> > > thte above specifications is causing a problem.
> > >
> > > Any ideas or help would be much appreiated
> > >
> > > regards
> > >
> > > Patrick

Regarding the desire to delete any row in a range which contains only
blank cells: the received wisdom has been to loop.

I tried to think of an efficient way. I didn't like looping
through all rows. I decided we could just look for areas that are
blank and delete them without looping. Then all that might be left
would be multi-row areas that turn out not to be empty in all cells
of the row's range. We could simply loop through those few and
decide. It took me some work, but I have code now that works. I
will put it up on a web page later this weekend. (The URL is in a
comment below, but so far there's nothing there.)

Improvements gladly solicited.

=dman=

'----------------------

Option Explicit
Sub DelEmptyRows()

' If all cells in a row within range are empty, delete row
' Ver. 1.0 by Dallman Ross, 8 Sep 2007; use freely
' Latest version: http://heliotropos.com/xl/code/samples.html
'
' Goal was to reduce looping to a minimum; _
hence, we only loop through multi-row "candidate" areas _
as needed

Dim iLastRow, iLastCol As Long
Dim startCell, myIsect, myRg As Range
Dim area As Range
Dim r As Long


Set startCell = Range("A1") ' change as desired

iLastRow = Cells(Rows.Count, startCell).End(xlUp).row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

On Error Resume Next
Set myRg = Range(startCell, Cells(iLastRow, iLastCol))

Set myIsect = Intersect(myRg, _
Columns(startCell).SpecialCells(xlCellTypeBlanks)). _
EntireRow.Areas ' all range rows with blanks in 1st col.


For Each area In myIsect
area.Activate
'Debug.Print area.row

If Selection.Find("?") = False Then
'// There were only blank cells in the range row(s)
Selection.EntireRow.Delete

Else
'// Loop through remaining multirow "candidate" ranges _
only as needed

'Debug.Print area.Rows.Count, area.row
For r = area.row To area.row + area.Rows.Count - 1
Intersect(area, Rows(r)).Select

If Selection.Find("?") = False _
Then Selection.EntireRow.Delete
Next 'r
End If
Next 'area
End Sub

'----------------------

====================================
In <fbrg3l$iuj$(E-Mail Removed)>, Dallman Ross <dman@localhost.>
spake thusly:

>
> Fair advice, methinks, Joel. Here's a different approach.
> I confess I had trouble following what the OP's actual desire
> is.
>
> Sub test2()
> ' delete entire rows if row range is blank
>
> Dim iLastRow, iLastCol As Long
> Dim iRow As Long
>
> iLastRow = Cells(Rows.Count, "C").End(xlUp).row
> iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
>
> For iRow = iLastRow To 1 Step -1
> With Range(Cells(iRow, "A"), Cells(iRow, iLastCol)).Activate
> On Error Resume Next
> If Not Selection.Find(What:="?").Activate Then
> Rows(iRow).Delete
> iLastRow = iLastRow - 1
> End If
> End With
> Next 'iRow
>
> ' use iLastRow to create the Totals row now as desired
> Debug.Print "iLastRow Is Now " & iLastRow
>
> End Sub
>
> =dman=
>
> ============================
> In <2074A011-6C36-4D54-8DAB-(E-Mail Removed)>, Joel
> <(E-Mail Removed)> spake thusly:
>
> > Inserting rows and deleting rows at the same time becomes a very
> > difficult task to get your loop counters correct. I recommend
> > that you do it in two passes. First Delete rows then add rows.
> >
> > When you add rows you usually have to have two counters. One to
> > keep track of the number of times to loop through the code. The
> > second to count the row number.
> >
> > When deleting you have to increment your loop counter only when
> > you are not deleting a row. Here is a simple version of the
> > delete
> >
> > Sub test()
> >
> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> >
> > RowCount = 1
> > For LoopCount = 1 To LastRow
> > LastColumn = Cells(RowCount, Columns.Count).Column
> > found = False
> > For ColumnCount = 1 To LastColumn
> > If Not IsEmpty(Cells(RowCount, ColumnCount)) Then
> > found = True
> > Exit For
> > End If
> > Next ColumnCount
> > If found = False Then
> > Rows(RowCount).Delete
> > Else
> > RowCount = RowCount + 1
> > End If
> >
> > Next LoopCount
> >
> > End Sub
> > "Patrick Bateman" wrote:
> >
> > > i have a sheet populated by data from a database query, parts
> > > of the rows of data are coppied into a different sheet with a
> > > totals row at the bottom. The problem is, the data i am using
> > > can range from 10 to hundreds of rows long, but the totals row
> > > always needs to be at the bottom of the data (next to it).
> > >
> > > what i really want to do is create a macro that inserts a row
> > > underneith a row which contains values (specifically values as
> > > all rows contain formulas) and deletes rows if the row above is
> > > empty.
> > >
> > > i have a macro that adds a new row and fills down the
> > > formulas only from the line above, but getting it to run to
> > > thte above specifications is causing a problem.
> > >
> > > Any ideas or help would be much appreiated
> > >
> > > regards
> > >
> > > Patrick

[Whoops, well, I found some incorrigible bugs in the first version of
the below. I'm superseding the article with ver. 1.1 of my code. The
problems were: (1) I forgot to use "Step -1" in the area loop for
multiple rows, so the looping got screwed up; and (2) it turns out
that ".Find("?") = FALSE" is satisfied with the number 0, but I
wanted only blank cells -- so I needed a workaround there. I
also removed some needless extra looping in this version. =dman=]

Regarding the desire to delete any row in a range which contains only
blank cells: the received wisdom has been to loop.

I tried to think of an efficient way. I didn't like looping
through all rows. I decided we could just look for areas that are
blank and delete them without looping. Then all that might be left
would be multi-row areas that turn out not to be empty in all cells
of the row's range. We could simply loop through those few and
decide. It took me some work, but I have code now that works. I
will put it up on a web page later this weekend. (The URL is in a
comment below, but so far there's nothing there.)

Improvements gladly solicited.

=dman=

'----------------------
Option Explicit
Sub DelEmptyRows()

' If all cells in a row within range are empty, delete row
' Ver. 1.1 by Dallman Ross, 8 Sep 2007; use freely
' Latest version: http://heliotropos.com/xl/code/samples.html
'
' Goal was to reduce looping to a minimum; _
hence, we only loop through multi-row "candidate" areas _
as needed

Dim iLastRow, iLastCol As Long
Dim startCell, myIsect, myRg As Range
Dim area As Range
Dim r As Long


Set startCell = Range("A1") ' change as desired

iLastRow = Cells(Rows.Count, startCell).End(xlUp).row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

On Error Resume Next
Set myRg = Range(startCell, Cells(iLastRow, iLastCol))

Set myIsect = Intersect(myRg, _
Columns(startCell).SpecialCells(xlCellTypeBlanks)). _
EntireRow.Areas ' all range rows with blanks in 1st col.


For Each area In myIsect
area.Activate
'Debug.Print area.row

If Selection.Find("?").Activate = False Then
If Selection <> 0 Then
'// There were only blank cells in the range row(s)
Selection.EntireRow.Delete
End If

ElseIf area.Rows.Count > 1 Then
'// Loop through remaining multirow "candidate" ranges _
only as needed

'Debug.Print area.Rows.Count, area.row
For r = area.row + area.Rows.Count - 1 To area.row Step -1
Intersect(area, Rows(r)).Select

If Selection.Find("?").Activate = False Then
If Selection <> 0 Then
Selection.EntireRow.Delete
End If
End If
Next 'r
End If
Next 'area

End Sub


'----------------------

====================================
In <fbrg3l$iuj$(E-Mail Removed)>, Dallman Ross <dman@localhost.>
spake thusly:

>
> Fair advice, methinks, Joel. Here's a different approach.
> I confess I had trouble following what the OP's actual desire
> is.
>
> Sub test2()
> ' delete entire rows if row range is blank
>
> Dim iLastRow, iLastCol As Long
> Dim iRow As Long
>
> iLastRow = Cells(Rows.Count, "C").End(xlUp).row
> iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
>
> For iRow = iLastRow To 1 Step -1
> With Range(Cells(iRow, "A"), Cells(iRow, iLastCol)).Activate
> On Error Resume Next
> If Not Selection.Find(What:="?").Activate Then
> Rows(iRow).Delete
> iLastRow = iLastRow - 1
> End If
> End With
> Next 'iRow
>
> ' use iLastRow to create the Totals row now as desired
> Debug.Print "iLastRow Is Now " & iLastRow
>
> End Sub
>
> =dman=
>
> ============================
> In <2074A011-6C36-4D54-8DAB-(E-Mail Removed)>, Joel
> <(E-Mail Removed)> spake thusly:
>
> > Inserting rows and deleting rows at the same time becomes a very
> > difficult task to get your loop counters correct. I recommend
> > that you do it in two passes. First Delete rows then add rows.
> >
> > When you add rows you usually have to have two counters. One to
> > keep track of the number of times to loop through the code. The
> > second to count the row number.
> >
> > When deleting you have to increment your loop counter only when
> > you are not deleting a row. Here is a simple version of the
> > delete
> >
> > Sub test()
> >
> > LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> >
> > RowCount = 1
> > For LoopCount = 1 To LastRow
> > LastColumn = Cells(RowCount, Columns.Count).Column
> > found = False
> > For ColumnCount = 1 To LastColumn
> > If Not IsEmpty(Cells(RowCount, ColumnCount)) Then
> > found = True
> > Exit For
> > End If
> > Next ColumnCount
> > If found = False Then
> > Rows(RowCount).Delete
> > Else
> > RowCount = RowCount + 1
> > End If
> >
> > Next LoopCount
> >
> > End Sub
> > "Patrick Bateman" wrote:
> >
> > > i have a sheet populated by data from a database query, parts
> > > of the rows of data are coppied into a different sheet with a
> > > totals row at the bottom. The problem is, the data i am using
> > > can range from 10 to hundreds of rows long, but the totals row
> > > always needs to be at the bottom of the data (next to it).
> > >
> > > what i really want to do is create a macro that inserts a row
> > > underneith a row which contains values (specifically values as
> > > all rows contain formulas) and deletes rows if the row above is
> > > empty.
> > >
> > > i have a macro that adds a new row and fills down the
> > > formulas only from the line above, but getting it to run to
> > > thte above specifications is causing a problem.
> > >
> > > Any ideas or help would be much appreiated
> > >
> > > regards
> > >
> > > Patrick