将单行数据拆分为多个唯一行，并拆分到一个新的工作表中，以包含标头作为值和单元格内容作为值 [英] split a single row of data into multiple unique rows into a new sheet to include headers as values and cell contents as values

问题描述

我发现了很多数据拆分VBA代码的帖子，但是没有一个按内容拆分它并提取标题并将结果放入新表中的数据(我需要将原始数据保留为原始状态).

I have found quite a lot of data splitting VBA code posts but none that splits it by contents and pulls in the header and puts the results into a new sheet (I need the original data to be left in its original state).

我有一个电子表格，其中包含以国家/地区为标题的列中的值，我需要将新表中的国家/地区中的国家/地区的值拆分为新工作表中的每一行.新行.

I have a spreadsheet that contains values in columns with a country as a header and I need to split each row into new rows in a new sheet with the country listed in a new column and the value from the same country column in the new row.

我想忽略单元格内容为空白的任何国家/地区标题，如第3行的示例所示.

I want to ignore any country headers where the cell contents are blank as in the example with row 3.

这是原始数据的一个小示例:

This is a small example of the raw data:

这是我需要将3行变成的内容:

And this is what I need the 3 rows to turn into:

尽管我在问VBA代码，但我还不知道如何编写VBA，只能调整我在互联网上找到的代码以适合我的情况.

Although I am asking about VBA code I do not yet know how to write VBA and only adjust code that I have found on the internet to fit my scenario.

推荐答案

由于您是VBA编码的新手，因此我使用For和If编写了一个简单的代码，请尝试学习此功能. 我没有声明每个变量，而是尝试声明它们并了解数据类型. last_row和last_column是可以在Excel VBA中大有帮助的变量，主要用于制作动态电子表格

Since you are new to coding VBA, i made a simple code with For and If, try to learn this functions. I didn't declare every variable, try to declare them and learn about data types. last_row and last_column are variables that can help a lot in Excel VBA, mainly to make dynamic spreadsheets

Sub test()
'Declare variables here
Dim sht1, sht2 As Worksheet

'sht1 has the data and sht2 is the output Worksheet, you can change the names
Set sht1 = ThisWorkbook.Worksheets("Sheet1")
Set sht2 = ThisWorkbook.Worksheets("Sheet2")

last_row1 = sht1.Range("A65536").End(xlUp).Row
last_column = sht1.Cells(1, sht1.Columns.Count).End(xlToLeft).Column
x = 1

'Add header
sht2.Cells(1, 1) = "Company"
sht2.Cells(1, 2) = "Country"
sht2.Cells(1, 3) = "Status"
'Data add
For i = 2 To last_row1
'Assuming Company is in Column 1 or A
    For k = 2 To last_column
        'If cell is different from blank then write data
        If sht1.Cells(i, k) <> "" Then
        sht2.Cells(x + 1, 1) = sht1.Cells(i, 1)
        sht2.Cells(x + 1, 2) = sht1.Cells(1, k)
        sht2.Cells(x + 1, 3) = sht1.Cells(i, k)
        x = x + 1
        End If
    Next k
Next i

MsgBox "Data Updated"
End Sub

下次，尝试学习一些编码并发布您的代码.

Next time, try to learn some coding and post your code.

这篇关于将单行数据拆分为多个唯一行，并拆分到一个新的工作表中，以包含标头作为值和单元格内容作为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！