I am trying to create a macro that will allow me to click checkboxes, they are dynamic so there will always be a different number of columns and checkboxes (could be A:G but also could be A: AA).
The macro needs to first read to see if the "TRUE" value exists in Column A, then look at Column B's value, it would then match that text value to the respective text value in Row 1. i.e., Reading and would NOT be deleted as A2 had been checked.
If it does not have a "TRUE" value, the respective column matching the adjacent cell in column B will be deleted. i.e., there was not a "TRUE" value in A3, so Grammar column got deleted out of the row.
Attatched are my code, an example of my spreadsheet, and what I want it to look like after the code is run.
Sub Main_Routine()
Dim ws As Worksheet
Dim i As Integer
Dim lr As range
Dim lc As range
Dim rng As range
Dim FindString As String
Dim lCell As range
Set ws = ThisWorkbook.Worksheets("Result")
Set lr = range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set lc = ws.Cells.Find(What:="*", after:=ws.Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
For i = lc.Column To 3 Step -1
If ((Application.WorksheetFunction.CountIfs(ws.range("A:A"), "TRUE")) And (Application.WorksheetFunction.CountIfs(ws.range("B:B"), (Cells(lr, 2) = Cells(1, i).Value)))) Then
Cells(1, i).EntireColumn.Hidden = False
Else
Cells(1, i).EntireColumn.Delete
End If
Next i
End Sub
Original dataset
Post Macro Dataset
Aucun commentaire:
Enregistrer un commentaire