Help with This code!!!

  • Thread starter Thread starter pauluk
  • Start date Start date
P

pauluk

Hi everyone yeah found out how to do blanks but jknow the code jus
won't do what i want it to do.

The columns ABC if column B is blank it deletes the content of A and
below is part of the code the if stement just repeats for EFG IJK MN
QRS

Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Set rng = Worksheets("Sheet1").Range("B:B")
Set rng2 = Worksheets("Sheet1").Range("F:F")
Set rng3 = Worksheets("Sheet1").Range("J:J")
Set rng4 = Worksheets("Sheet1").Range("N:N")
Set rng5 = Worksheets("Sheet1").Range("R:R")
If rng.Value = "" Then
Range("A:A").Select
Selection.ClearContents
Range("C:C").Select
Selection.ClearContents
End I
 
are you trying to process a whole column on a row by row basis? If so, you
have to loop through all the rows and check each one individually.

Dim rng(0 to 4) as Range, cell as Range
Set rng(0) = Worksheets("Sheet1").Range("B:B")
Set rng(1) = Worksheets("Sheet1").Range("F:F")
Set rng(2) = Worksheets("Sheet1").Range("J:J")
Set rng(3) = Worksheets("Sheet1").Range("N:N")
Set rng(4) = Worksheets("Sheet1").Range("R:R")
for i = 0 to 4
set rng(i) = rng(i).SpecialCells(xlBlanks)
for each cell in rng(i)
cell.offset(0,1).ClearContents
cell.offset(0,-1).ClearContents
next
Next
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Paul,

Is this what you want?

Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Set rng = Worksheets("Sheet1").Range("B:B")
Set rng2 = Worksheets("Sheet1").Range("F:F")
Set rng3 = Worksheets("Sheet1").Range("J:J")
Set rng4 = Worksheets("Sheet1").Range("N:N")
Set rng5 = Worksheets("Sheet1").Range("R:R")
If WorksheetFunction.CountA(rng) = 0 Then
Range("A:A").ClearContents
Range("C:C").ClearContents
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If you are talking to me -

Code works fine for me.

Having problems is not very descriptive.

Do you have merged cells? If so, that could be a problem.
 
Back
Top