If Value in Range then Delete Column
 

Hi,

I have values in the range C1:J149 on Sheet 2.
Via code, I wish to look at the values in the range C1:J1 and if the
value is also in the range D27:D34 on Sheet 1, then delete the
respective column accomodating that value in Sheet 2.

Other Info:
Both ranges are dynamic, in that not all cells will have values.


eg
Sheet 2 Range "C1" = 200535
Sheet 2 Range "D1" = 200536
Sheet 2 Range "E1" = 200537
Sheet 1 Range "D27" = 200535
Sheet 1 Range "D28" = 200536

Result:
Sheet 2 Column "C" (200535) deleted.
Sheet 2 Column "D" (200536) deleted.

All other data in Sheet 2 remains intact.


Any help appreciated.

Cheers

Bob

If Value in Range then Delete Column
 

Try:

Sub Test()
Dim r As Range, c As Range
Dim DRng As Range

Set r = Sheets("Sheet1").Range("D27:D34")
For Each c In Sheets("Sheet2").Range("C1:J1")
If Len(c) 0 And Application.CountIf(r, c.Value) 0 Then
If DRng Is Nothing Then Set DRng = c Else Set DRng = Union(DRng, c)
End If
Next
If Not DRng Is Nothing Then DRng.EntireColumn.Delete
End Sub

Regards,
Greg

"Ozbobeee" wrote:

Hi,

I have values in the range C1:J149 on Sheet 2.
Via code, I wish to look at the values in the range C1:J1 and if the
value is also in the range D27:D34 on Sheet 1, then delete the
respective column accomodating that value in Sheet 2.

Other Info:
Both ranges are dynamic, in that not all cells will have values.


eg
Sheet 2 Range "C1" = 200535
Sheet 2 Range "D1" = 200536
Sheet 2 Range "E1" = 200537
Sheet 1 Range "D27" = 200535
Sheet 1 Range "D28" = 200536

Result:
Sheet 2 Column "C" (200535) deleted.
Sheet 2 Column "D" (200536) deleted.

All other data in Sheet 2 remains intact.


Any help appreciated.

Cheers

Bob