Selecting Columns based on a number criteria

Question

I am trying to zero out data for x number of columns based on a criteria.
For example:

Sheet 1:

Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec

Sheet 2:

Version (Criteria) = 2

So, I would like to select the first two months and zero out all data in Jan & Feb column.
Any help would be appreciated.

Update: I got this much so far, now I am stuck on selecting every

<Code>
Sub hmm()
Dim cell As Range
For Each cell In Sheets("Test").Range("B10:B10")

mths = cell
Sheets("Report Data").Select
Range("C4").Select

ActiveCell.Offset(0, mths).Select
Dy = ActiveCell.Column

Range("C5").Select
Selection.End(xlDown).Select

bl = ActiveCell.Row
Range("D4:Dy4" & bl).Select

Next
End Sub
</Code>

I cant get it to select the range.

It is stored in cell C4. I was able to get it to select the month but for some reason i can not make the selection work. — Sagar

Gary's Student Gary's Student · Accepted Answer · 2014-04-09T16:32:44

This code gets the number of columns from cell C4 in Sheet2.

It then sets to zero the cells in Sheet1 in those columns, but not the first row:

Sub ZeroData()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim N As Long, M As Long
    M = s2.Range("C4").Value
    With s1
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A2", .Cells(N, M)).Value = 0
    End With
End Sub

Selecting Columns based on a number criteria

1 Answers