help again please tom?!

[sds]

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

 

[Tom Ogilvy]

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

 

[Martyn Kay]

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!

 

[Tom Ogilvy]

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

 

[Martyn Kay]

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!