CODEWITHSUNDEEP
excelvbacountadditionweek-number
USER7423
USER7423

Reputation: 173

VBA count columns and copy them

Below code hide a certain number of rows(depending on the number of the week we are in) and shows only the cell corresponding to the current week number and cells corresponding to the future week numbers. 

Dim test As String
test = Format(Now, "yyyy", vbMonday) & KW(Now)

For k = 3 To lastColumn

        ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).ColumnWidth = cWidth

        If ThisWorkbook.Worksheets(PlanningTableNameUG).Cells(1, k).Value = test Then

            today = True
            On Error Resume Next
                ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k - 1).Ungroup
            On Error GoTo 0
            ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k - 1).Group

        End If

        If Not today Then
            On Error Resume Next
                ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Ungroup
            On Error GoTo 0
            ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Group
            ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Hidden = True

            If Hidden = True Then
                ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Group.Copy
                ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Group.Insert Shift:=xlToRight

            End If

        Else
            ThisWorkbook.Worksheets(PlanningTableNameUG).Columns(k).Hidden = False
        End If

Next k

' calculate the week number
Function KW(d As Date) As Integer
  Dim Tag As Long
  Tag = DateSerial(Year(d + (8 - Weekday(d)) Mod 7 - 3), 1, 1)
  KW = (d - Tag - 3 + (Weekday(Tag) + 1) Mod 7) \ 7 + 1
End Function

Now, I need to count how many columns were hidden and add the exact numbers of columns, example:

columns: 1,2,3,4,5,6,7,8,9,10 (column 4 is corresponding to today week number)

I hide: 1,2,3 because there are in the past weeks, and want to add 11,12, 13, together with the week number corresponding for them, but not more than 1 year from current date.

Count of the weeks starts at the begging of the current year.

Upvotes: 1

Views: 226

Answers (2)

Xabier
Xabier

Reputation: 7735

When hiding the columns you could add an integer variable that increases by one then use this variable to add that many new columns.

Then to add the week number, use the last column with the week number on it and add one to it for each of the new columns...

Upvotes: 0

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

To count hidden columns in used range of ThisWorkbook.Worksheets(PlanningTableNameUG): 

Dim col As Range
Dim cnt As Long

For Each col In ThisWorkbook.Worksheets(PlanningTableNameUG).UsedRange
    Debug.Print col.EntireColumn.Hidden
    cnt = cnt - col.EntireColumn.Hidden
Next col

EntireColumn.Hidden returns True if is hidden. True is -1 in VBA, that is why i deduct it from the counter to get positive value.

Upvotes: 0

Related Questions