How to extract filled column values per row to another sheet in Excel

Question

I have an Excel file with hundreds of columns and rows. For each row, only approximately five or six columns are applicable and are filled with some value. I want to copy the filled columns (including header) per row to a new sheet and drop the empty ones, so for example:

Original sheet:

Type of Fruit	Green	Yellow	Red	Brown
Apple	Sometimes		Yes
Pear				Yes
Banana		Yes

Desired sheet:

Type of Fruit
Apple	Green	Red
	Sometimes	Yes
Pear	Brown
	Yes
Banana	Yellow
	Yes

Any help would be greatly appreciated.

Please refer to [How do I ask a good question](https://stackoverflow.com/help/how-to-ask) and [Help Center](https://stackoverflow.com/help) for guidance on how to ask a good question. Please include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). — LeoE, Jan 18 '21 at 11:19
Please include the code and/or the formulas you have tried so far with a description of what is wrong with them and you will get an answer in no time. You can [edit](https://stackoverflow.com/posts/65772225/edit) your post at any time. — VBasic2008, Jan 18 '21 at 11:53
Well.. This website is for helping people code for themselves, not to do all the work for them. But I'm in a good mood.. So, I think I'll try helping you. — Gass, Jan 18 '21 at 13:45
Either try if a Pivot Table fits your needs (this would be the easiest way, do some research on that) or you need to write a VBA code that transforms this into the desired layout (a lot of work and VBA skills are needed). • Note that this is no code writing service, so you would have to do that on your own. Still you can ask questions here if you have written some code and got stuck or erros and you have a question to your code. See [ask]. — Pᴇʜ, Feb 03 '21 at 08:22

Gass · Accepted Answer · 2021-02-03T11:54:24.167

This should do it. You can change the values in the config section to make some adjustments if needed.

Sub organize()

Dim row_header As Integer, total_cols As Long, col_n As Long
Dim target_next_row As Long, target_next_col As Long
Dim row_n As Long, new_sheet As String, main As String
Dim counter As Long, db_col_start As Integer, last_row As Long

'CONFIG
'---------------------------
row_header = 1            'in what row is the header?
db_col_start = 1          'in what col number does the data start?
main = "Sheet1"           'sheet name with the data set
new_sheet = "New Sheet"   'name of the sheet to create
'---------------------------

target_next_row = 3

'create new sheet
Sheets.Add.Name = new_sheet

'tranfer "Type of Fruit" title to new sheet
Sheets(new_sheet).Cells(1, 1) = Sheets(main).Cells(row_header, db_col_start)
Sheets(new_sheet).Cells(1, 1).Font.Bold = True

'get the total number of columns & rows
total_cols = _
Sheets(main).Cells(row_header, Columns.count).End(xlToLeft).Column

last_row = _
Sheets(main).Cells(Rows.count, db_col_start).End(xlUp).Row

For counter = 1 To last_row - row_header
    Sheets(new_sheet).Cells(counter * 3, 1) = _
    Sheets(main).Cells(row_header + counter, db_col_start)
Next counter

For row_n = row_header + 1 To last_row
    target_next_col = 2
    For col_n = db_col_start + 1 To total_cols
    
        If IsEmpty(Sheets(main).Cells(row_n, col_n)) = False Then
            Sheets(new_sheet).Cells(target_next_row, target_next_col) = _
            Sheets(main).Cells(row_header, col_n)
            Sheets(new_sheet).Cells(target_next_row, target_next_col).Font.Bold = True
            Sheets(new_sheet).Cells(target_next_row + 1, target_next_col) = _
            Sheets(main).Cells(row_n, col_n)
            target_next_col = target_next_col + 1
        End If
    
    Next col_n
    target_next_row = target_next_row + 3
Next row_n
   
End Sub

Row counting variables need to be of type `Long`. Excel has more rows than `Integer` can handle. Actually there is no benefit in using `Integer` in VBA and you can [always use `Long` instead](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) (unless you need to access some old API). — Pᴇʜ, Feb 03 '21 at 08:28
I use integer usually for numbers I know won't go above 30 thousand, mainly for less memory usage. — Gass, Feb 03 '21 at 11:57
@Gassz You can but you have no advantage by using `Integer` instead of `Long` for those numbers. I would use another criteria to decide the type: 1. Which one does take less memory (both types take the same). 2. Which type causes less (implicit) type conversions. Here `Long` takes less conversions because even if `row_header` will never exceed `Integer` it now needs to be converted from `Integer` to `Long` when using it like that `For row_n = row_header + 1` so here you have an implicit `Integer` to `Long` conversion. If you define all as `Long` you have no type conversion (=advantage!). — Pᴇʜ, Feb 03 '21 at 12:27
Thank you for that. I did not know. Is that the case for bytes as well? So it's just better to use always Long then. — Gass, Feb 03 '21 at 12:30
@Gassz According to [the 3ʳᵈ answer](https://stackoverflow.com/a/51689021/3219613) in the link I gave, it looks like this applies to type `Byte` too. — Pᴇʜ, Feb 04 '21 at 10:35

How to extract filled column values per row to another sheet in Excel

1 Answers1