Posted to microsoft.public.excel.programming
|
|
Annoying delete empty rows question
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
|