Delete blank rows
 

Hi
Wonder if you can help. I would like to put in some code to automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows above
this are blank, delete them.

Can anyone help? I do have the following code already in and would like to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes

Delete blank rows
 

Hi Marie

Try this:

Private Sub workbook_open()
Sheets("Sheet2").Activate
Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

Regards,
Per

"Marie Bayes" skrev i meddelelsen
...
Hi
Wonder if you can help. I would like to put in some code to automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows above
this are blank, delete them.

Can anyone help? I do have the following code already in and would like
to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes

Delete blank rows
 

Sub del()

pozycja = Application.WorksheetFunction.Match("Date", Columns(1))

If IsEmpty(Cells(pozycja - 1, 1)) And IsEmpty(Cells(pozycja - 2, 1))
Then
Range(pozycja - 2 & ":" & pozycja - 1).Rows.EntireRow.Delete
End If

End Sub



On 29 Kwi, 14:30, Marie Bayes
wrote:
Hi
Wonder if you can help. *I would like to put in some code to automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows above
this are blank, delete them.

Can anyone help? *I do have the following code already in and would like to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
* * Cells.Select
* * With Selection
* * * * .HorizontalAlignment = xlGeneral
* * * * .VerticalAlignment = xlBottom
* * * * .WrapText = False
* * * * .Orientation = 0
* * * * .AddIndent = False
* * * * .IndentLevel = 0
* * * * .ShrinkToFit = False
* * * * .ReadingOrder = xlContext
* * * * .MergeCells = False
* * End With
* * Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes

Delete blank rows
 

Thanks, that only deleted the first instance in the column, do you know if
there's a way to do it for every recurrence of "Date" in the column?
--
Marie Bayes


"Per Jessen" wrote:

Hi Marie

Try this:

Private Sub workbook_open()
Sheets("Sheet2").Activate
Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

Regards,
Per

"Marie Bayes" skrev i meddelelsen
...
Hi
Wonder if you can help. I would like to put in some code to automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows above
this are blank, delete them.

Can anyone help? I do have the following code already in and would like
to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes

Delete blank rows
 

Marie,

This will loop through all occurences of Date in column A.

Private Sub workbook_open()
Dim FirstMatch As Range
Dim TargetRange As Range
Sheets("Sheet1").Activate
Set f = Columns("A").Find(What:="Date", After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
Set FirstMatch = f
Do
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
Set f = Columns("A").FindNext(After:=f)
Debug.Print f.Address & " " & FirstMatch.Address
Loop Until f.Address = FirstMatch.Address


With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

--
Per

"Marie Bayes" skrev i meddelelsen
...
Thanks, that only deleted the first instance in the column, do you know if
there's a way to do it for every recurrence of "Date" in the column?
--
Marie Bayes


"Per Jessen" wrote:

Hi Marie

Try this:

Private Sub workbook_open()
Sheets("Sheet2").Activate
Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

Regards,
Per

"Marie Bayes" skrev i meddelelsen
...
Hi
Wonder if you can help. I would like to put in some code to
automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows
above
this are blank, delete them.

Can anyone help? I do have the following code already in and would
like
to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes

Delete blank rows
 

That's brilliant - did the trick perfectly - thanks very much
--
Marie Bayes


"Per Jessen" wrote:

Marie,

This will loop through all occurences of Date in column A.

Private Sub workbook_open()
Dim FirstMatch As Range
Dim TargetRange As Range
Sheets("Sheet1").Activate
Set f = Columns("A").Find(What:="Date", After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
Set FirstMatch = f
Do
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
Set f = Columns("A").FindNext(After:=f)
Debug.Print f.Address & " " & FirstMatch.Address
Loop Until f.Address = FirstMatch.Address


With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

--
Per

"Marie Bayes" skrev i meddelelsen
...
Thanks, that only deleted the first instance in the column, do you know if
there's a way to do it for every recurrence of "Date" in the column?
--
Marie Bayes


"Per Jessen" wrote:

Hi Marie

Try this:

Private Sub workbook_open()
Sheets("Sheet2").Activate
Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)
If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With
End Sub

Regards,
Per

"Marie Bayes" skrev i meddelelsen
...
Hi
Wonder if you can help. I would like to put in some code to
automatically
(on open) do the following:

Find the cell (in column A) with the word "Date" in, if the two rows
above
this are blank, delete them.

Can anyone help? I do have the following code already in and would
like
to
add the above to it:
Private Sub workbook_open()

Sheets("Sheet2").Select
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
End Sub

--
Marie Bayes