Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help for Deleting Blank Rows & Clearing Cell Contents
I have several columns of data in a worksheet (A1:I200), that is hard coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and so on Sometimes there is no data in the source cells which is resulting in many blank rows in the destination worksheet. I found the following macro that does delete blank rows, but the entire row must be empty for it to work Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub I am able to get rid of the formula’s by doing a paste value, however the cells are obviously not truly empty as the macro is not working. (If I manually select the cells and press the delete key the macro works) Does anyone know a way of a macro that I can use to clear the cell contents of these cells, or any suggestions on how to get rid if these blank unwanted rows? Thanks Karen -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=500949 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help for Deleting Blank Rows & Clearing Cell Contents
Dave Peterson suggest selecting the Range and doing
Edit=Replace Replace What: <Leave Blank Replace With" $$$$$ then do it again, but reversed Replace What: $$$$$ Replace With: <leave blank If you need code, turn on the macro recorder while you do it manually. -- Regards, Tom Ogilvy "ksp" wrote in message ... I have several columns of data in a worksheet (A1:I200), that is hard coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and so on Sometimes there is no data in the source cells which is resulting in many blank rows in the destination worksheet. I found the following macro that does delete blank rows, but the entire row must be empty for it to work Public Sub DeleteBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0 Then Rng.Rows(R).EntireRow.Delete End If Next R EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub I am able to get rid of the formula's by doing a paste value, however the cells are obviously not truly empty as the macro is not working. (If I manually select the cells and press the delete key the macro works) Does anyone know a way of a macro that I can use to clear the cell contents of these cells, or any suggestions on how to get rid if these blank unwanted rows? Thanks Karen -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=500949 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help for Deleting Blank Rows & Clearing Cell Contents
Thanks Tom Strangely enough I had just been playing with using the replace function and had just gotten it to work Thanks though for your input -- ksp ------------------------------------------------------------------------ ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267 View this thread: http://www.excelforum.com/showthread...hreadid=500949 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for clearing cell contents | Excel Discussion (Misc queries) | |||
Clearing out blank rows/columns | Excel Worksheet Functions | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
Deleting rows dependent upon cell contents | Excel Programming | |||
Deleting rows if cell is blank | Excel Programming |