Posts by djecris

Quote from royUK

Try this

Thank you!!

Quote from royUK

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

Hi royUK and thank you again for your great help on the "hide columns" macro.

I have another difficulty and I have been trying to adapt the macro you gave me without success.

Instead of defining a target cell, I'd like to have 2 of them (it represents a range of years). Let me illustrate to make it clear:

if A1 = 2022 and C1 = 2023, then we hide columns R to W

if A1 = 2023 and C1 = 2025, then we hide columns I to N and U to W

etc.

I guess it requires an adaptation of the solution you gave me for my previous request but I cannot find the solution. Could you please help to put me on the right track?

Quote from royUK

I didn't notice before but you have the code in Module 1 and the WorkBook event. I edited the code in Module 1 before

THANK YOU ! working perfectly

Quote from royUK

Try this amended code

Sorry Roy, but I have the same problem: when I filter by Sales for ex and I enter a value: all columns appear

Quote from royUK

You need to remove the the merged cells and replace with Center Across Selection. I have done this for the first 3 departments.

Check the amended code

thank you so much! there is one last problem though: every time I modify a cell in excel: all columns appear back automatically, any idea why?

Quote from royUK

You have MergedCells that don't allow the columns to be hidden as you expect.

You should NEVER use MergedCells, they will invariably cause problems.

Why have you got multiple entries for 2021 Q4?

Also, why haven't you used the Data Validation List that I suggested?

Display More

Oh ok thanks for that, I didn't know.

It's still not working though, do you know why when I type "Marketing", it doesn't show the desired columns? I guess there is something wrong :

Case "Marketing"

Columns("J:N").EntireColumn.Hidden = False

Columns("E:I,O:BC").EntireColumn.Hidden = True

full code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("E:I").EntireColumn.Hidden = False
Columns("J:BC").EntireColumn.Hidden = True
Case "Marketing"
Columns("J:N").EntireColumn.Hidden = False
Columns("E:I,O:BC").EntireColumn.Hidden = True
Case "Supply"
Columns("O:S").EntireColumn.Hidden = False
Columns("E:N,T:BC").EntireColumn.Hidden = True
Case Else: Columns("E:BC").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Quote from royUK

That's actually my code!

Where have you used it?

Attach an example of your workbook containing the code.

I've attached the excel file in this message.

Sorry I've copied paste the wrong code. The code I wanted to post is the one below:

Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("I:P").EntireColumn.Hidden = False
Columns("C:H").EntireColumn.Hidden = True
Case "Marketing"
Columns("C:H").EntireColumn.Hidden = False
Columns("I:P").EntireColumn.Hidden = True
Case Else: Columns("C:P").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Up,

does someone know what is wrong with my code below? I would really appreciate some help here,

Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$A$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("I:P").EntireColumn.Hidden = False
Columns("C:H").EntireColumn.Hidden = True
Case "Marketing"
Columns("C:H").EntireColumn.Hidden = False
Columns("I:P").EntireColumn.Hidden = True
Case Else: Columns("C:P").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Quote from royUK

Try this

I'm almost there thank you!!

Can you please tell me with the code below why when I write "Sales" or "Marketing", I have the left-hands columns that are not hidden ? (the columns on the right are hidden, but the ones on the left side with Supply from "E to I" are still visible and not hidden)

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$13" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Supply"
Columns("E:I").EntireColumn.Hidden = False
Columns("J:BC").EntireColumn.Hidden = True
Case "Sales"
Columns("J:N").EntireColumn.Hidden = False
Columns("E:I,M:BC").EntireColumn.Hidden = True
Case "Marketing"
Columns("O:S").EntireColumn.Hidden = False
Columns("E:N,T:BC").EntireColumn.Hidden = True
Case Else: Columns("E:BC").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Quote from royUK

Why can't you adapt the code?

I have tried this and it was not working, will check your proposition

Private Sub Worksheet_Change(ByVal Target As String)
Dim rng As String
Set rng = Target.Parent.String("B2")
If Intersect(Target, rng) Is Nothing Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Cells.EntireColumn.Hidden = False
Select Case rng
Case "Marketing"
Range("C1,E1,G1,I1,K1").EntireColumn.Hidden = True
Case "Sales"
Range("D1,F1,H1,J1,L1").EntireColumn.Hidden = True
End Select


Set rng = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Thank you for the answer but I cannot adapt it properly unfortunately

Anyone else to give me a quick solution? It's basically hiding/unhiding different columns based on a text in a cell

Hi all,

I've been researching for an answer but didn't find one solving my problem yet.

Context:

- I'm building a file for team members to enter some data for their category (Sales, Marketing, HR, etc.) per quarter (Q1 2022, Q2 2022, etc)

- For now: all categories are displayed in columns: Sales from column C to column H, Marketing from column I to columns P, etc. for the other categories (so the file has many columns to fit all categories).

Objective:

- I would like to facilitate the usage of the file. Let's say if I write "Sales" in cell A1: I want only column C to H to appear, if I write "Marketing" in cell A1: I want only column I to P to appear, etc.

- Ideally, I would like the macro to run automatically when the text is entered in A1

I've tried to create a macro but it's wrong (the columns are hidden when I enter a value in A1, but when I change the text in A1, nothing happens)

Can someone help please?

Quote from royUK

This should do what you want. You just need to decide how to run it.

Code

Sub HideRows()
    If IsEmpty(Range("A1")) Then Exit Sub
    Dim iX As Integer
    iX = Range("A1").Value - 1
    Rows("4:23").EntireRow.Hidden = True
    Rows("4:" & iX + 4).EntireRow.Hidden = False
End Sub

it's ok, just found out how to trigger it! thanks again!

Quote from royUK

This should do what you want. You just need to decide how to run it.

Code

Sub HideRows()
    If IsEmpty(Range("A1")) Then Exit Sub
    Dim iX As Integer
    iX = Range("A1").Value - 1
    Rows("4:23").EntireRow.Hidden = True
    Rows("4:" & iX + 4).EntireRow.Hidden = False
End Sub

it works that's great thank you!! Is there a way to trigger the Macro every time the cell A1 value is changed?

Quote from royUK

Why do you need to hide rows? A Table allows you to add data and hit tab to add a new row with all formulas and formats added.

Hi Roy, thanks for the answer. It's a document for general usage (including many people not familiar with excel): i'm trying to make it as user friendly (automatizing it) as much as possible.

Quote from Justin Doward

Hi DJ,

Your scenario would be a lot easier to "imagine" if you provided an example sheet. In what you have written you are suggesting a column full of names, in column A but then you want to hide and show column A. What is happening to the names here? Do you just want a list of names, each name can have any number of lines inserted between one name and the next depending on a selected team size?

Whatever the scenario an example sheet will save a lot of time.

thanks for the answer,

Here is a file attached illustrating what I mean. Hope it's clearer,

Hi all,

I've been researching for an answer but didn't find one solving my problem.

Context: I'm building a file for people to enter all their team members names in a table. I want the table to be tailored to the number of team members : let's say that they enter in cell A1 the number of members in their team. If they insert the number 5 in A1: the table would be 5 lines. If they insert the number 30, the table would be 30 lines etc. Simplest way is then to pre-create the table and to build a macro to hide lines based on cell A1 value.

So in this context could you please explain how to create this macro?

Imagine the table goes from row 6 to row 100 and column A to H (all team members name would be in column A of the table)

If user enters a value in A1, and if value is 1: rows 7 to 100 would be hidden (leaving only line 6 in the table, for 1 team member), if the value entered in A1 is 2: row 8 to 100 would be hidden (leaving 2 lines), etc and etc.

I guess I would need to create a variable but I'm a bit lost here.

Can someone help?