PDA

View Full Version : [SOLVED] Check Column N and if empty clean data on columns M,L,K



parscon
09-01-2014, 06:07 AM
Dears
I need a VBA code that check column N and if there is not any data on this column clean data on columns M , L , K and this is very important i do not want delete the column just clean data on these columns.
Also I need this VBA have loop ability .
Thanks for your help

gmayor
09-01-2014, 06:39 AM
Sub CleanCols()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "N").End(xlUp).Row
If LastRow = 1 Then
Range("K:M").ClearContents
End If
End Sub

Should do the job. Where does the loop come into it?

parscon
09-01-2014, 06:48 AM
Dear gmayor , thanks for your help but the code does not work , and it must check the column N if it is empty it will clean column K To M .

parscon
09-01-2014, 06:50 AM
I have this below code and this code will clean column M i need to cloean also L and K column



Sub Clear()
Dim r As Range
For Each r In Intersect(Range("N:N"), ActiveSheet.UsedRange)
If IsEmpty(r) Then
r.Offset(0, -1).Clear
End If
Next
End Sub

gmayor
09-01-2014, 07:04 AM
In what way doesn't it work? My guess is that you have a header row? In which case change
If LastRow = 1 Then
to
If LastRow = 2 Then

parscon
09-01-2014, 07:15 AM
This Code Working .



Sub Clear()
Dim r As Range
For Each r In Intersect(Range("N:N"), ActiveSheet.UsedRange)
If IsEmpty(r) Then
r.Offset(0, -1).Clear
r.Offset(0, -2).Clear
r.Offset(0, -3).Clear
End If
Next
End Sub

mancubus
09-01-2014, 08:11 AM
another way:



Sub cc()
With Worksheets("Sheet1") 'change worksheet name to suit
.AutoFilterMode = False
.Cells(1).AutoFilter Field:=14, Criteria1:="="
.UsedRange.Columns("K:M").Offset(1).SpecialCells(12).ClearContents
.AutoFilterMode = False
End With
End Sub