How to project data from 1 sheet to another but remove columns with no data below header.

[johnmpc]

Hi All,

I have an input sheet for placing clothing orders. I then have a separate output sheet that displays the data in a way i can copy and paste what is needed to send back out.

I have Small medium large Xlarge XXlarge sizes in column headers, but what i want is if any columns below the header are empty i don't want to display the column or header.

Is that possible?

 

[My Aswer Is This]

What columns are we dealing with?
I assume you always want to keep column A shown.
Do you have a set number of column Like A to J
Or is it always different?
And if we hide certain columns how do you plan to show them later if needed

 

[johnmpc]

Hi Thanks for the response.

Here is the columns in the input sheet.

I don't always need column 1 no. I just want to show on the output sheet any column that has an input in the column.

Ideally this will be dynamic so will automatically update the output sheet as the data is entered in the input sheet.

Is that clearer?

One Size

S

M

L

XL

2XL

3XL

4XL

5XL

OTHER

Decoration 1

Position 1

Decoration 2

Position 2

Decoration 3

Postition 3

 

[Joe4]

Try this code:

Sub MyDeleteColumns()

    Dim lc As Long
    Dim c As Long
    Dim lr As Long
  
    Application.ScreenUpdating = False
  
'   Find last column in row 1 with data (last header)
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
  
'   Loop through all columns
    For c = lc To 1 Step -1
'       Check to see if last row in column is 1
        If Cells(Rows.Count, c).End(xlUp).Row = 1 Then
'           Delete column
            Columns(c).Delete
        End If
    Next c
  
    Application.ScreenUpdating = True
  
End Sub

This will delete the unused columns. If you want this to happen on a different sheet, just copy the data over to a new sheet first, and then run this code on that sheet.
(You could even get that most of that code with the Macro Recorder, if you like).

 

[johnmpc]

Struggling to get this working.
I was thinking maybe there was a better way not using VBA.
Would it be possible to only "project" the columns that have data in?

 

[My Aswer Is This]

Struggling to get this working.
I was thinking maybe there was a better way not using VBA.
Would it be possible to only "project" the columns that have data in?

Define project

 

[Joe4]

Struggling to get this working.

What part can't you get working?
Is it the copying over to a new sheet?

If so, if you provide the details, I can add that to my code. I just need to know the following:
- What is the name of the sheet where the data currently resides?
- Does the new sheet already exist, or do we need to create one?
If it already exists, what is that sheets name?

 

[My Aswer Is This]

You said:
Ideally this will be dynamic so will automatically update the output sheet as the data is entered in the input sheet.
Now this could happen with a sheet change script. If you enter "Alpha" In Range ("A1") the same value could be entered in your sheet range("A1") but this would require using Vba.
Only action you would take is entering the value into the sheet.

 

[johnmpc]

Define project

Currently just displaying the cells from the first sheet to the second sheet using =

All the data is on the first sheet, I just want to change how its laid out and omit certain bits on a 2nd sheet.

 

[Joe4]

Currently just displaying the cells from the first sheet to the second sheet using =

All the data is on the first sheet, I just want to change how its laid out and omit certain bits on a 2nd sheet.

Can you please answer the questions I asked in my previous post, and I can amend my code for this part?