Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing empty rows issue Please Help!
Hi Everyone,
I have about 10 different tabs in same format in my workbook and having some difficulties with my macro. Basically, My macro runs through column("B:B") and if the cell is empty it will remove entire row, if not then it returns column D's value with matching row. Sub Clean() Dim ws As Worksheet, lngRow As Long For Each ws In Sheets For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 If ws.Range("B" & lngRow) = "" Then ws.Rows(lngRow).Delete Else ws.Range("D" & lngRow) = ws.Range("D" & lngRow) End If Next Next End Sub How do I alter this code to return column "D", "F" and "H:AF"? Thank you so much for your help. Regards, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing empty rows issue Please Help!
James;607927 Wrote: Hi Everyone, I have about 10 different tabs in same format in my workbook and having some difficulties with my macro. Basically, My macro runs through column("B:B") and if the cell is empty it will remove entire row, if not then it returns column D's value with matching row. Sub Clean() Dim ws As Worksheet, lngRow As Long For Each ws In Sheets For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 If ws.Range("B" & lngRow) = "" Then ws.Rows(lngRow).Delete Else ws.Range("D" & lngRow) = ws.Range("D" & lngRow) End If Next Next End Sub How do I alter this code to return column "D", "F" and "H:AF"? Thank you so much for your help. Regards, James To try to answer your question add these 2 lines after ws.Range("D" & lngRow) = ws.Range("D" & lngRow): Code: -------------------- ws.Range("F" & lngRow) = ws.Range("F" & lngRow) ws.Range("H" & lngRow & ":AF" & lngRow) = ws.Range("H" & lngRow & ":AF" & lngRow) -------------------- but then it returns column D's value with matching row puzzles me. The sub returns diddly-squat as far as I can tell. What is the line ws.Range("D" & lngRow) = ws.Range("D" & lngRow) actually supposed to be doing? -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168661 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing empty rows issue Please Help!
If I know that the last row will always be B2200, how do I alter the
code? will it run significantly faster? On Jan 11, 12:21*pm, "michdenis" wrote: Hi, Try this : '----------------------------------- Sub Clean() Dim ws As Worksheet, LastRow As Long On Error Resume Next For Each ws In Worksheets * * With ws * * * * .Range("B:B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete * * * * LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row * * * * With .Range("D1:F" & LastRow) * * * * * * .Value = .Value * * * * End With * * * * With .Range("H1:AF" & LastRow) * * * * * * .Value = .Value * * * * End With * * *End With Next End Sub '----------------------------------- "James" a écrit dans le message de groupe de discussion : ... Hi Everyone, I have about 10 different tabs in same format in my workbook and having some difficulties with my macro. Basically, My macro runs through column("B:B") and if the cell is empty it will remove entire row, if not then it returns column D's value with matching row. Sub Clean() Dim ws As Worksheet, lngRow As Long For Each ws In Sheets * * For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 * * *If ws.Range("B" & lngRow) = "" Then * * * * *ws.Rows(lngRow).Delete * * Else * * * * ws.Range("D" & lngRow) = ws.Range("D" & lngRow) * * End If Next Next End Sub How do I alter this code to return column "D", "F" and "H:AF"? Thank you so much for your help. Regards, James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing empty rows issue Please Help!
| will it run significantly faster?
No. Nothing noticeable But if you want to adapt, you could do this : '---------------------------------- Sub Clean() Dim ws As Worksheet On Error Resume Next For Each ws In Worksheets With ws .Range("B1:B2200").SpecialCells(xlCellTypeBlanks). EntireRow.Delete With .Range("D1:F2200") .Value = .Value End With With .Range("H1:AF2200") .Value = .Value End With End With Next End Sub '---------------------------------- "James" a écrit dans le message de groupe de discussion : ... If I know that the last row will always be B2200, how do I alter the code? will it run significantly faster? On Jan 11, 12:21 pm, "michdenis" wrote: Hi, Try this : '----------------------------------- Sub Clean() Dim ws As Worksheet, LastRow As Long On Error Resume Next For Each ws In Worksheets With ws .Range("B:B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row With .Range("D1:F" & LastRow) .Value = .Value End With With .Range("H1:AF" & LastRow) .Value = .Value End With End With Next End Sub '----------------------------------- "James" a écrit dans le message de groupe de discussion : ... Hi Everyone, I have about 10 different tabs in same format in my workbook and having some difficulties with my macro. Basically, My macro runs through column("B:B") and if the cell is empty it will remove entire row, if not then it returns column D's value with matching row. Sub Clean() Dim ws As Worksheet, lngRow As Long For Each ws In Sheets For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1 If ws.Range("B" & lngRow) = "" Then ws.Rows(lngRow).Delete Else ws.Range("D" & lngRow) = ws.Range("D" & lngRow) End If Next Next End Sub How do I alter this code to return column "D", "F" and "H:AF"? Thank you so much for your help. Regards, James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing empty cells | Excel Discussion (Misc queries) | |||
Removing Empty Rows from Worksheets | Excel Discussion (Misc queries) | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
Finding criteria and removing matching rows (Range issue?) | Excel Programming | |||
Removing Empty Rows and selecting Specific Rows | Excel Programming |