CODEWITHSUNDEEP
excelvba
MayankG
MayankG

Reputation: 57

What to use instead of .EntireColumn

I'm trying to create a simple counting sheet which has 5 buttons to increment the count of each field, a 'New Round' Button to start a new round in a new column (R2 and so forth) and Clear button which clears all the counts and start over again from Round1

Screenshot

So far I've come up with this code:

    Function rngLastRound() As Range
    With Range("2:2").Cells(1, Columns.Count).End(xlToLeft)
        Set rngLastRound = .EntireColumn
    End With
End Function
Sub IncrementCurrentRoundOfRow(N As Long)
    With rngLastRound
        .Cells(N, 1).Value = Val(CStr(.Cells(N, 1).Value)) + 1
    End With
End Sub
Sub IncrementCurrentRoundA()
    Call IncrementCurrentRoundOfRow(3)
End Sub
Sub IncrementCurrentRoundB()
    Call IncrementCurrentRoundOfRow(4)
End Sub
Sub IncrementCurrentRoundC()
    Call IncrementCurrentRoundOfRow(5)
End Sub
Sub IncrementCurrentRoundD()
    Call IncrementCurrentRoundOfRow(6)
End Sub
Sub IncrementCurrentRoundE()
    Call IncrementCurrentRoundOfRow(7)
End Sub
Sub NewRound()
    With rngLastRound.Offset(0, 1)
        .Cells(2, 1).Value = "R" & (.Column - 1)
        .Cells(3, 1).Resize(5, 1).Value = 0
    End With
End Sub
Sub Clear()
    Range("B2", rngLastRound).ClearContents
    Call NewRound
End Sub

The code works fine, but it clears the entire columns so that means the totals and grand total also gets cleared. How do I prevent this from happening by not specifying .EntireColumn attribute and instead a specific range?

Thanks

Upvotes: 1

Views: 68

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

A Game

Option Explicit

Function rngLastRound() As Range
    With Range("2:2").Cells(Columns.Count).End(xlToLeft)
        Set rngLastRound = .Resize(6)
    End With
End Function
Sub IncrementCurrentRoundOfRow(N As Long)
    With rngLastRound
        .Cells(N).Value = Val(CStr(.Cells(N).Value)) + 1
    End With
End Sub
Sub IncrementCurrentRoundA()
    Call IncrementCurrentRoundOfRow(2)
End Sub
Sub IncrementCurrentRoundB()
    Call IncrementCurrentRoundOfRow(3)
End Sub
Sub IncrementCurrentRoundC()
    Call IncrementCurrentRoundOfRow(4)
End Sub
Sub IncrementCurrentRoundD()
    Call IncrementCurrentRoundOfRow(5)
End Sub
Sub IncrementCurrentRoundE()
    Call IncrementCurrentRoundOfRow(6)
End Sub
Sub NewRound()
    With rngLastRound.Offset(, 1)
        .Cells(1).Value = "R" & (.Column - 1)
        .Cells(2).Resize(5).Value = 0
    End With
End Sub
Sub Clear()
    Range("B2", rngLastRound).ClearContents
    Call NewRound
End Sub

Upvotes: 1

Алексей Р
Алексей Р

Reputation: 7627

Try changing the line Set rngLastRound = .EntireColumn in the rngLastRound () function to Set rngLastRound = Application.Intersect (.EntireColumn, Rows ("2: 7"))

Upvotes: 1

Related Questions