Clear Column Contents Based on Criteria

eclark62 · Jun 1st 2010

I have 7 columns - E,F,G,H,J,K,L that have either a 1 or 0 value with a sum formula at the bottom of the column.

I would like to create a script to clear the contents of those columns but leave the formulas and also remove the data in the row's of M,N,O

I have gone throught the clear contents threads but I am lost.

Thanks for all your help.

Eric

**PCI** · Jun 1st 2010

A sample will help to prepare the script.
Show what the result expected

gollem · Jun 1st 2010

Hi,

here's an example, this should give you a start:

Code

Dim lngRow As Long
Dim lngStartRow As Long
 
lngStartRow = 2 'Starting row
lngRow = lngStartRow
Do While Left(ActiveWorkbook.ActiveSheet.Range("E" & lngRow).Formula, 1) <> "=" 'Search until formule
lngRow = lngRow + 1
Loop
 
ActiveWorkbook.ActiveSheet.Range("E" & lngStartRow & ":L" & lngRow - 1).ClearContents

eclark62 · Jun 2nd 2010

Re: Clear contents in Column based on criteria

Thanks Gollem got the data in the columns to clear!! Now I am having probelms getting it to clear off the data in Columns M-O where there is data in E-L. When I added my script to remove the data in M-O it is also removing the data in Column I which it is not supposed to do dont know what I did wrong.

here is my code:

' My code to remove text in column M-O for every entry into column E-L with the exception Column I

Code

Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Dim cell As Range
  For Each cell In Application.Intersect(ActiveSheet.Range("e:h"), _
         ActiveSheet.UsedRange)
    If Trim(cell.Value) = "1" Or "0" Then
        ActiveSheet.Range(cell, cell.Offset(8, 10)).ClearContents
    End If
  Next
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
    
    
' Gollems code


    Dim lngRow As Long
    Dim lngStartRow As Long
    
    lngStartRow = 2 'Starting row
    lngRow = lngStartRow
    Do While Left(ActiveWorkbook.ActiveSheet.Range("E" & lngRow).Formula, 1) <> "=" 'Search until formule
    lngRow = lngRow + 1
            
    Loop
    
    ActiveWorkbook.ActiveSheet.Range("E" & lngStartRow & ":H" & lngRow - 2).ClearContents
    ' skip column I
    ActiveWorkbook.ActiveSheet.Range("J" & lngStartRow & ":L" & lngRow - 2).ClearContents

Display More

example attached

gollem · Jun 2nd 2010

Re: Clear contents in Column based on criteria

I'll take a look at it.

Please adapt your post so that the code is bewteen code-tags, it's better to read that way.

Clear Column Contents Based on Criteria

Participate now!

Share