Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 "GBExcel via OfficeKB.com" <u55438@uwe a écrit dans le message de groupe de discussion : a1d823bed10f9@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a1d823bed10f9@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 "GBExcel via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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, -- Data Hog "GBExcel via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 "Don Guillett" wrote in message ... 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 "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a1d823bed10f9@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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, -- Data Hog "GBExcel via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Table row with first cell not empty
For archival purposes, post your final
-- Don Guillett Microsoft MVP Excel SalesAid Software "GBExcel via OfficeKB.com" <u55438@uwe wrote in message news:a1dfe4e4aa4a6@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/201001/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If cell empty delete row | Excel Discussion (Misc queries) | |||
How to delete at once empty columns in a table? | Excel Discussion (Misc queries) | |||
Delete rows if particular cell empty | Excel Programming | |||
Code to Delete Empty Rows in a Word Table | Excel Programming | |||
how to run through a table an delete the empty cells? | Excel Programming |