Creating Results Table + Hiding columns based on text in specific row

[MelissaBr]

Hi guys.

I am trying to make a nice testing/results file at work.
The idea is that when we get a request, the person responsible for the testing will copy the test list from a separate request file (test marked with X needs to be performed) to a sheet called "info", then they specify how many samples to test and with a button it will update the sheet called "results" to have the correct test shown in the table with the proper sample amount and hide the sheets from tests that don't need to be tested.

I tried do create something of my own, but it's not going as planned.

For the "info" sheet, it looks like this.

Not all tests will need to be added in the table in the "results" sheet (prep work), so my plan was to hide those to make it look a bit neater.

Next, we have the results sheet.
This is just a table with all possible tests.

In a different file, we have a very simple macro which will add rows to the table so it will have the correct amount of samples. When using that code for this one, things seem to break a little. I have to add a blank row below (which I then make 0 pixels tall), otherwise it will create extra rows with wrong numbering. (In the following picture I wanted 5 samples, as you can see this did not happen). I have no clue why this happens (Something to do with the rows below?).

See the code below:

Dim X, amount, W As Integer
Dim samples As Variant

samples = Worksheets("Info").Range("C2").Value
amount = samples - 1

Application.ScreenUpdating = False

Sheets("results").Select
    X = 8
        Do Until X = 8 + amount
        Rows(X).Select
        Selection.Copy
        Rows(X + 1).Select
        Selection.Insert shift:=xlDown
        X = X + 1
        Loop

To make sure it will number correctly, Cell"B8", has the following formula: = IF(B7 = "Nr";1;B7 + 1).

Then, there's the whole hiding of columns and sheets part where I tried something by myself. It does work, but if a new test gets added or a current one gets removed, it will break (Cause I am selecting a specific range, which will no longer correspond to that test if columns get added or removed in front of this).

If Sheets("Info").Range("C23").Value = "" Then
    Sheets("203").Visible = False
    Sheets("Results").Columns("H:I").Hidden = True
End If

So then my thought went like this: If a test with code "102" is NOT marked with "X" in the "info" sheet, search for "102" in row 3 of the "results" sheet and hide that column and the sheet (if it has any). This works (just need to change the row numbers for the "info" sheet if something changes there), but when it comes to tests that have multiple columns it doesn't work.
Below my code for this.

This one works, cause only 1 column is involved

If Sheets("Info").Range("C18").Value = "" Then
    Sheets("Results").Select
    For W = 1 To 100
        If Cells(3, W).Value = "102" Then
           Columns(W).Hidden = True
        End If
    Next W
End If

The next one will only hide the first column, keeping the second one unhidden

If Sheets("Info").Range("C22").Value = "" Then
    Sheets("Results").Select
    For Each W In Range("C3:CM3").Cells
        If W.Value = "202" Then
        W.EntireColumn.Hidden = True
        Sheets("202").Visible = False
End If
    Next W
End If

Also. some sheets will have multiple tests assigned to them (701 + 702 for example). My guess is to add and "OR" part to the code to make sure it work, right?

Once the table is finished, all visible sheets need to have a table with the correct amount of rows corresponding the samples to be tested. I will make a separate post for that once this is in place and I have given it a go myself

Could someone help me with my problems above and maybe explain it a bit so I can figure it out by myself next time?
If there are any questions, or if something is unclear, please let me know and I can explain it a bit more

Help would be much appreciated!
-Melissa

 

[DanteAmor]

Hi @MelissaBr.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

​

I made 2 macros, one to insert rows and another to hide columns and sheets. I don't know if you want them together or separate.

Before running the macro to insert rows we need to configure something in the macro and/or in your sheet.

This is the image of your "Results" sheet

​

In cell B10 (first cell in blue) you can see that I added the word "Total", this text is to identify the end of the area where the rows will be inserted, so if in that cell (first cell in blue), you must put the text "Total" or if in that cell you already have a text, then put that text in this line of the macro:

Set f = shR.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)

Macro1:

Sub insert_rows()
  Dim shR As Worksheet
  Dim f As Range
  Dim n As Long, amount As Long

  Application.ScreenUpdating = False
  
  amount = 8 + Sheets("Info").Range("C2").Value - 1
  
  Set shR = Sheets("Results")
  Set f = shR.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    n = f.Row
    If n > 10 Then
      shR.Rows("10:" & n - 1).Delete Shift:=xlUp
    End If
    
    shR.Rows("8:9").ClearContents
    shR.Range("B8").Value = 1
    If amount > 8 Then
      shR.Rows("8:8").Copy
      shR.Rows("9:" & amount).Insert Shift:=xlDown
      shR.Range("B8:B" & amount).DataSeries xlColumns, xlLinear, xlDay, 1, Trend:=False
    End If
  End If
  
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub

Macro2:

Sub Hidden_Columns_and_Sheets()
  Dim shI As Worksheet, shR As Worksheet, sh As Worksheet
  Dim i As Long
  Dim f As Range
  Dim nCode As String
  
  Application.ScreenUpdating = False
  
  Set shI = Sheets("Info")
  Set shR = Sheets("Results")
  
  shR.Cells.EntireColumn.Hidden = False
  For Each sh In Sheets
    sh.Visible = xlSheetVisible
  Next
  
  For i = 18 To shI.Range("A" & Rows.Count).End(3).Row
    nCode = shI.Range("A" & i).Value
    If nCode <> "" And shI.Range("C" & i).Value = "" Then
      Set f = shR.Range("3:3").Find(nCode, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        If f.MergeCells Then
          f.MergeArea.EntireColumn.Hidden = True
        Else
          f.EntireColumn.Hidden = True
        End If
      End If
      On Error Resume Next
      Sheets(nCode).Visible = False
      On Error GoTo 0
    End If
  Next
  
  Application.ScreenUpdating = True
End Sub

If you want the macros together, you could call them like this:

Sub macros()
  Call insert_rows
  Call Hidden_Columns_and_Sheets
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[MelissaBr]

Hi @DanteAmor,
Thank you for the nice welcome and the 2 macros!

I tried applying them both to the original file and they are performing 90% of the way I would like. The 10% are my own fault for not thinking it would form a problem.

Insert rows macro:
In my original post, I had an extra empty row 9. I had to add this because otherwise my own code didn't work. If the code is made without this workaround, I wouldn't need this of course. I changed 1 line and this seems to work just fine.

shR.Rows("8").ClearContents

Not sure if I need to change something else, but I am not seeing big problems with this.

The rows are separated by a normal line. Any way to make this a dotted line, or would this be too much of a hassle?
I can also use my workaround from before if it's too much work.

I get this:

, but would like this:

Hide rows + sheets macro:
In my test file this one works perfect. However, I completely forgot that in the official file, the sheets will have the codes(001, 102, etc.), but also a name behind it, so you know which test it is.

Because of this, the macro won't work of course . Any solution for this?
The name will not be the same as column "B" from the "Info" sheet, since it will be way too long to use.

Thanks again!

-Melissa

 

[DanteAmor]

In my original post, I had an extra empty row 9.

That's right, so my macro considers that row blank.

The rows are separated by a normal line. Any way to make this a dotted line, or would this be too much of a hassle?

I completely forgot that in the official file, the sheets will have the codes(001, 102, etc.), but also a name behind it, so you know which test it is.

So my macro doesn't work at all.

I suggest you rewrite your request using the actual information. That way I will look for a complete solution.

 

[MelissaBr]

I suggest you rewrite your request using the actual information. That way I will look for a complete solution.

I made a new thread and updated all the info.

Would really appreciate it if you could have another look at it