Delete Table row with first cell not empty

[GBExcel via OfficeKB.com]

Hi,

I need a macro to do the following:

I have a table like this, starting in row 6.


] ] A B C D E
6] 1 N N N
7] 2 N N N N
8] 3 N N N N
9] 4
10]5
11]
12]
13]
etc.
100]

Rows 11 to 100 are empty.

I want to select area A1:E100 and delete all content from table rows A9:E9
and A10:E10, because these table rows have empty cells from columns B through
to E. The rows are marked by an ID number as in numbers 1 to 5 above. When
cells in columns B to E are found to be empty, the content of the table row,
including the row ID, must be deleted. Note, this is not deleting the entire
excel row, just the content of rows 9 and 10 or down.

Appreciate the help.

GBExcel

 

[Charabeuh]

2 macros:
the first macro finds the first empty row (col B to col E) and deletes all
rows from this first empty row to row 100
the 2nd macro delete rows ( row by row ) skipping rows that are not empty
(col B to col E) from row 6 to row 100


Option Explicit

Public Sub deleteEmptyRows()

Dim RowMin, I, J

'Find the line from which to delete
RowMin = 6
With ActiveSheet
For I = 2 To 5
J = 1 + Cells(101, I).End(xlUp).Row
RowMin = Application.WorksheetFunction.Max(J, RowMin)
Next I
If RowMin <= 100 Then Range("A" & RowMin & ":E100").ClearContents
End With

End Sub


Public Sub deleteEmptyRows2()

Dim I, NbrNotEmpty

With ActiveSheet
For I = 6 To 100
NbrNotEmpty = Application.WorksheetFunction.CountA(Range("B" & I & ":E" &
I))
If NbrNotEmpty = 0 Then Range("A" & I).ClearContents
Next I
End With

End Sub

 

[Charabeuh]

read
the first macro finds the empty row (col B to col E) from which all other
rows are empty.
Then deletes all rows from this first empty row to row 100


instead of
the first macro finds the first empty row (col B to col E) and deletes all
rows from this first empty row to row 100

2 macros:
the first macro finds the first empty row (col B to col E) and deletes all
rows from this first empty row to row 100
the 2nd macro delete rows ( row by row ) skipping rows that are not empty
(col B to col E) from row 6 to row 100


Option Explicit

Public Sub deleteEmptyRows()

Dim RowMin, I, J

'Find the line from which to delete
RowMin = 6
With ActiveSheet
For I = 2 To 5
J = 1 + Cells(101, I).End(xlUp).Row
RowMin = Application.WorksheetFunction.Max(J, RowMin)
Next I
If RowMin <= 100 Then Range("A" & RowMin & ":E100").ClearContents
End With

End Sub


Public Sub deleteEmptyRows2()

Dim I, NbrNotEmpty

With ActiveSheet
For I = 6 To 100
NbrNotEmpty = Application.WorksheetFunction.CountA(Range("B" & I & ":E"
& I))
If NbrNotEmpty = 0 Then Range("A" & I).ClearContents
Next I
End With

End Sub

Hi,

I need a macro to do the following:

I have a table like this, starting in row 6.


] ] A B C D E
6] 1 N N N
7] 2 N N N N
8] 3 N N N N
9] 4
10]5
11]
12]
13]
etc.
100]

Rows 11 to 100 are empty.

I want to select area A1:E100 and delete all content from table rows
A9:E9
and A10:E10, because these table rows have empty cells from columns B
through
to E. The rows are marked by an ID number as in numbers 1 to 5 above.
When
cells in columns B to E are found to be empty, the content of the table
row,
including the row ID, must be deleted. Note, this is not deleting the
entire
excel row, just the content of rows 9 and 10 or down.

Appreciate the help.

GBExcel

 

[Don Guillett]

If it as you say then you could simply select col B and filter for blanks
and delete based on that. Record a macro if desired.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Charabeuh]

2 macros:
the first macro finds the empty row (col B to col E) from which all other
rows are empty.
Then deletes all rows from this first empty row to row 100

the 2nd macro delete rows ( row by row ) skipping rows that are not empty
(col B to col E) from row 6 to row 100


Option Explicit

Public Sub deleteEmptyRows()

Dim RowMin, I, J

'Find the line from which to delete
RowMin = 6
With ActiveSheet
For I = 2 To 5
J = 1 + Cells(101, I).End(xlUp).Row
RowMin = Application.WorksheetFunction.Max(J, RowMin)
Next I
If RowMin <= 100 Then Range("A" & RowMin & ":E100").ClearContents
End With

End Sub


Public Sub deleteEmptyRows2()

Dim I, NbrNotEmpty

With ActiveSheet
For I = 6 To 100
NbrNotEmpty = Application.WorksheetFunction.CountA(Range("B" & I & ":E" &
I))
If NbrNotEmpty = 0 Then Range("A" & I).ClearContents
Next I
End With

End Sub

 

[J_Knowles]

Sub ClearContentsTableBlanks()
' checks A-E rows 6 thru 100 for blanks, and clear contents of row
For i = 6 To 100
For j = 1 To 5
If IsEmpty(Cells(i, j).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Exit For
End If
Next j
Next i
Range("A1:E100").Select
End Sub

HTH,

 

[Don Guillett]

Based on a copy of the workbook and details of needs.

Option Explicit
Sub deleterowsSAS()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, "n").End(xlUp).Row To 69 Step -1
If Len(Application.Trim(Cells(i, "n"))) < 1 Then
'Rows(i).Delete' if entirerow
Range(Cells(i, "m"), Cells(i, "y")).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

If it as you say then you could simply select col B and filter for blanks
and delete based on that. Record a macro if desired.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi,

I need a macro to do the following:

I have a table like this, starting in row 6.


] ] A B C D E
6] 1 N N N
7] 2 N N N N
8] 3 N N N N
9] 4
10]5
11]
12]
13]
etc.
100]

Rows 11 to 100 are empty.

I want to select area A1:E100 and delete all content from table rows
A9:E9
and A10:E10, because these table rows have empty cells from columns B
through
to E. The rows are marked by an ID number as in numbers 1 to 5 above.
When
cells in columns B to E are found to be empty, the content of the table
row,
including the row ID, must be deleted. Note, this is not deleting the
entire
excel row, just the content of rows 9 and 10 or down.

Appreciate the help.

GBExcel

 

[J_Knowles]

Sub RemoveTableBlanks()
' checks A-E rows 6 thru 100 for blanks, and clear contents of row
For i = 6 To 100
For j = 1 To 5
If IsEmpty(Cells(i, j).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Exit For
End If
Next j
Next i
Range("A1:E100").Select
End Sub


HTH,

 

[GBExcel via OfficeKB.com]

Thank you to all who gave support on this. It is great to know that there is
someone to turn to to make one's life a lot easier. A special thanks to Don.

I've managed to get it to work with some tweeking.

Appreciate the help.

GBExcel

 

[Don Guillett]

For archival purposes, post your final