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

johnmpc

New Member
Joined
Oct 19, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,925
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 19, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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 SizeSMLXL2XL3XL4XL5XLOTHERDecoration 1Position 1Decoration 2Position 2Decoration 3Postition 3
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,451
Office Version
  1. 365
Platform
  1. Windows
Try this code:
VBA 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

New Member
Joined
Oct 19, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,925
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,451
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,925
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 19, 2020
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,451
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,144,098
Messages
5,722,454
Members
422,436
Latest member
deadlock123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top