Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help again please tom?!
Im using this macro, i dont get any errors but it doesnt
clear rows a or b if rows c:u are unpopulated which is what im aiming for. Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+Shift+S ' ChDir "C:\Documents and Settings\sds\Desktop" Workbooks.Open FileName:= _ "C:\Documents and Settings\sds\Desktop\MORNINGPREALERT.xls" Range("A1:U7").Select Selection.ClearContents Cells.Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlUp Dim rng As Range, cell As Range Set rng = Range(Range("C1"), Cells(Rows.Count, 3).End (xlUp)) For Each cell In rng If Application.CountA(cell.Resize(1, 18)) = 0 Then Cells(cell.Row, 1).Resize(1, 2).ClearContents End If Next Cells.Select Selection.Cut Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs FileName:= _ "C:\Documents and Settings\sds\Desktop\MORNING LABELS.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close ActiveWindow.Close End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help again please tom?!
You delete all the blank cells with this:
Cells.Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlUp just before my code, so there is not point running the code I gave you since you have destroyed any situation which would determine clear cells in A and B. Regards, Tom Ogilvy "sds" wrote in message ... Im using this macro, i dont get any errors but it doesnt clear rows a or b if rows c:u are unpopulated which is what im aiming for. Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+Shift+S ' ChDir "C:\Documents and Settings\sds\Desktop" Workbooks.Open FileName:= _ "C:\Documents and Settings\sds\Desktop\MORNINGPREALERT.xls" Range("A1:U7").Select Selection.ClearContents Cells.Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Delete Shift:=xlUp Dim rng As Range, cell As Range Set rng = Range(Range("C1"), Cells(Rows.Count, 3).End (xlUp)) For Each cell In rng If Application.CountA(cell.Resize(1, 18)) = 0 Then Cells(cell.Row, 1).Resize(1, 2).ClearContents End If Next Cells.Select Selection.Cut Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs FileName:= _ "C:\Documents and Settings\sds\Desktop\MORNING LABELS.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close ActiveWindow.Close End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help again please tom?!
tom,
I have tried putting your code in before i delete or select any of the blank cells which still didnt work? Many thanks for your patience martyn *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help again please tom?!
Sub Tester5()
Dim rng As Range, cell As Range Set rng = Range(Range("C1"), Cells(Rows.Count, 3).End(xlUp)) For Each cell In rng If Application.CountA(cell.Resize(1, 18)) = 0 Then Cells(cell.Row, 1).Resize(1, 2).ClearContents End If Next End Sub worked fine for me. I suspect your cells only look blank but are not. A more cumbersom approach, but perhaps one that will work would be Sub Tester5a() Dim rng As Range, cell As Range Set rng = Range(Range("C1"), Cells(Rows.Count, 3).End(xlUp)) For Each cell In rng cnt = 0 For Each cell1 In cell.Resize(1, 18) If Len(Trim(cell1.Text)) = 0 Then cnt = cnt + 1 End If Next If cnt = 18 Then Cells(cell.Row, 1).Resize(1, 2).ClearContents End If Next End Sub -- Regards Tom Ogilvy "Martyn Kay" wrote in message ... tom, I have tried putting your code in before i delete or select any of the blank cells which still didnt work? Many thanks for your patience martyn *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help again please tom?!
I have tried the new code which you said would cumbersome but it didnt work. ABCDEFGHIJKLMNOP xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xx xx xx xx xx xx This is how my spreadsheet looks. It had loads of empty rows underneath but my code clears them. What i want your code to do is clear the A+B columns if there is no data C to P for e.g. Then i would love you forever! :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|