Hi there - please forgive the newbie question, I've been searching
around on these boards for an answer and nothing seems to quite fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that multiple
people will be using as a basis to add their own data into. (i.e. the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to the
point where I have all the blank rows based on 2 columns displayed
fine.

My question is - how can I automate the selection of a variable amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

look in vba help for specialcells

"Tamsen" wrote in message
om...
Hi there - please forgive the newbie question, I've been searching
around on these boards for an answer and nothing seems to quite fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that multiple
people will be using as a basis to add their own data into. (i.e. the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to the
point where I have all the blank rows based on 2 columns displayed
fine.

My question is - how can I automate the selection of a variable amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

SpecialCells works fine if I am selecting rows to delete based on a
criteria of a blank cell in 1 column, as soon as I try to make this
judgement based on 2 columns it does not work.

Data format is such

Row 1 Column A Column B Column C
Row 2 data data
Row 3 data data
Row 4
Row 5 data data data
Row 6 data data

So I'm doing an auto filter on Column A and Column B to look for
blanks in the 2 columns ONLY - and then figure out how to select those
rows. In the example above, I want it to delete Row 4 only.

OR - I use the SpecialCells suggestion below:-
Columns("A:B").Activate
Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Del ete - this is the
line that does not work. I get a run time error "'1004' - cannot use
that command on overlapping sections'.

If I change the above to say just Columns("A:A") or ("B:B") it does
delete based on that criteria, but doesn't work for my purposes (it
deletes row 4 and row 3, or row 4 and row 6.)

Still a newbie, and still need some guidance :(

Many thanks in advance
"Don Guillett" wrote in message ...
look in vba help for specialcells

"Tamsen" wrote in message
om...
Hi there - please forgive the newbie question, I've been searching
around on these boards for an answer and nothing seems to quite fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that multiple
people will be using as a basis to add their own data into. (i.e. the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to the
point where I have all the blank rows based on 2 columns displayed
fine.

My question is - how can I automate the selection of a variable amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

Aren't they different ways of doing the same thing?

Tom Ogilvy wrote:

I think Dave meant to say:

Sub TestTwo()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng = Range("A:A").SpecialCells(xlBlanks)
Set rng1 = Range("B:B").SpecialCells(xlBlanks)
If Not rng Is Nothing And Not rng1 Is Nothing Then
Set rng2 = Intersect(rng.EntireRow, rng1.EntireRow)
rng2.Select
Else
MsgBox "No Rows meet the criteria"
End If
End Sub

Change rng2.Select to rng2.Delete when you are satisfied this does what you
want.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Can you pick a column to determine the last used row?

If yes, then here's one way:

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'header rows???
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, "A")) _
And IsEmpty(.Cells(iRow, "B")) Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you can't pick out a column that always has data, then you could use:

LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
instead.


Tamsen wrote:

SpecialCells works fine if I am selecting rows to delete based on a
criteria of a blank cell in 1 column, as soon as I try to make this
judgement based on 2 columns it does not work.

Data format is such

Row 1 Column A Column B Column C
Row 2 data data
Row 3 data data
Row 4
Row 5 data data data
Row 6 data data

So I'm doing an auto filter on Column A and Column B to look for
blanks in the 2 columns ONLY - and then figure out how to select those
rows. In the example above, I want it to delete Row 4 only.

OR - I use the SpecialCells suggestion below:-
Columns("A:B").Activate
Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Del ete - this is the
line that does not work. I get a run time error "'1004' - cannot use
that command on overlapping sections'.

If I change the above to say just Columns("A:A") or ("B:B") it does
delete based on that criteria, but doesn't work for my purposes (it
deletes row 4 and row 3, or row 4 and row 6.)

Still a newbie, and still need some guidance :(

Many thanks in advance
"Don Guillett" wrote in message
...
look in vba help for specialcells

"Tamsen" wrote in message
om...
Hi there - please forgive the newbie question, I've been searching
around on these boards for an answer and nothing seems to quite fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that
multiple
people will be using as a basis to add their own data into. (i.e.
the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to the
point where I have all the blank rows based on 2 columns displayed
fine.

My question is - how can I automate the selection of a variable
amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

--

Dave Peterson


--

Dave Peterson

It was a joke <g

But yes, I hope they both end up with the same result.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
Aren't they different ways of doing the same thing?

Tom Ogilvy wrote:

I think Dave meant to say:

Sub TestTwo()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng = Range("A:A").SpecialCells(xlBlanks)
Set rng1 = Range("B:B").SpecialCells(xlBlanks)
If Not rng Is Nothing And Not rng1 Is Nothing Then
Set rng2 = Intersect(rng.EntireRow, rng1.EntireRow)
rng2.Select
Else
MsgBox "No Rows meet the criteria"
End If
End Sub

Change rng2.Select to rng2.Delete when you are satisfied this does what
you
want.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Can you pick a column to determine the last used row?

If yes, then here's one way:

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'header rows???
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, "A")) _
And IsEmpty(.Cells(iRow, "B")) Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you can't pick out a column that always has data, then you could
use:

LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
instead.


Tamsen wrote:

SpecialCells works fine if I am selecting rows to delete based on a
criteria of a blank cell in 1 column, as soon as I try to make this
judgement based on 2 columns it does not work.

Data format is such

Row 1 Column A Column B Column C
Row 2 data data
Row 3 data data
Row 4
Row 5 data data data
Row 6 data data

So I'm doing an auto filter on Column A and Column B to look for
blanks in the 2 columns ONLY - and then figure out how to select
those
rows. In the example above, I want it to delete Row 4 only.

OR - I use the SpecialCells suggestion below:-
Columns("A:B").Activate
Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Del ete - this is the
line that does not work. I get a run time error "'1004' - cannot
use
that command on overlapping sections'.

If I change the above to say just Columns("A:A") or ("B:B") it does
delete based on that criteria, but doesn't work for my purposes (it
deletes row 4 and row 3, or row 4 and row 6.)

Still a newbie, and still need some guidance :(

Many thanks in advance
"Don Guillett" wrote in message
...
look in vba help for specialcells

"Tamsen" wrote in message
om...
Hi there - please forgive the newbie question, I've been
searching
around on these boards for an answer and nothing seems to quite
fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that
multiple
people will be using as a basis to add their own data into.
(i.e.
the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on
at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to
the
point where I have all the blank rows based on 2 columns
displayed
fine.

My question is - how can I automate the selection of a variable
amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

--

Dave Peterson


--

Dave Peterson

I feel better now. (I thought humor, but then I thought of all the special
things my special version of excel does! <vbg)

Tom Ogilvy wrote:

It was a joke <g

But yes, I hope they both end up with the same result.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
Aren't they different ways of doing the same thing?

Tom Ogilvy wrote:

I think Dave meant to say:

Sub TestTwo()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
On Error Resume Next
Set rng = Range("A:A").SpecialCells(xlBlanks)
Set rng1 = Range("B:B").SpecialCells(xlBlanks)
If Not rng Is Nothing And Not rng1 Is Nothing Then
Set rng2 = Intersect(rng.EntireRow, rng1.EntireRow)
rng2.Select
Else
MsgBox "No Rows meet the criteria"
End If
End Sub

Change rng2.Select to rng2.Delete when you are satisfied this does what
you
want.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
Can you pick a column to determine the last used row?

If yes, then here's one way:

Option Explicit
Sub testme01()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'header rows???
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, "A")) _
And IsEmpty(.Cells(iRow, "B")) Then
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you can't pick out a column that always has data, then you could
use:

LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
instead.


Tamsen wrote:

SpecialCells works fine if I am selecting rows to delete based on a
criteria of a blank cell in 1 column, as soon as I try to make this
judgement based on 2 columns it does not work.

Data format is such

Row 1 Column A Column B Column C
Row 2 data data
Row 3 data data
Row 4
Row 5 data data data
Row 6 data data

So I'm doing an auto filter on Column A and Column B to look for
blanks in the 2 columns ONLY - and then figure out how to select
those
rows. In the example above, I want it to delete Row 4 only.

OR - I use the SpecialCells suggestion below:-
Columns("A:B").Activate
Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Del ete - this is the
line that does not work. I get a run time error "'1004' - cannot
use
that command on overlapping sections'.

If I change the above to say just Columns("A:A") or ("B:B") it does
delete based on that criteria, but doesn't work for my purposes (it
deletes row 4 and row 3, or row 4 and row 6.)

Still a newbie, and still need some guidance :(

Many thanks in advance
"Don Guillett" wrote in message
...
look in vba help for specialcells

"Tamsen" wrote in message
om...
Hi there - please forgive the newbie question, I've been
searching
around on these boards for an answer and nothing seems to quite
fit.

Scenario:-
I have a worksheet template (although not an .xlt file) that
multiple
people will be using as a basis to add their own data into.
(i.e.
the
amount of data varies per workbook).

I want to make sure that people don't leave blank rows (based on
at
least 2 columns of info).

So - I have been going down the autofilter route, and I get to
the
point where I have all the blank rows based on 2 columns
displayed
fine.

My question is - how can I automate the selection of a variable
amount
of blank rows to delete them?

Ideally I would love a VBA solution, as I'll have approx 18
spreadsheets to complete this on at least 2 or 3 times a week...
URGH!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson