来自动态范围表的唯一列表与可能的空白 [英] Unique list from dynamic range table with possible blanks

问题描述

我在sheet1中有Excel表，其中A列：

I have an Excel table in sheet1 in which column A:

公司名称公司1
公司2

公司
3
公司1

公司4
公司1
公司
3

Name of company
Company 1
Company 2

Company 3
Company 1

Company 4
Company 1
Company 3

我想在列A中为sheet2提取公司名称的唯一列表。我只能在如果我在公司名称之间没有任何空格，但是当我有一个更多的公司是空白的时候，帮助列。

I want to extract a unique list of company names to sheet2 also in column A. I can only do this with help of a helper column if I dont have any blanks between company names but when I do have I get one more company which is a blank.

另外，我已经研究过但是例子是非动态表，所以它不工作，因为我不知道我的列的长度。

Also, I've researched but the example was for non-dynamic tables and so it doesn't work because I don't know the length of my column.

我想在Sheet2列A：

I want in Sheet2 Column A:

公司名称公司1
公司2
公司3 _
公司4

Name of company
Company 1
Company 2
Company 3
Company 4

寻找需要较少计算能力的Excel或Excel-VBA的解决方案。他们在sheet2中出现的最后一个顺序并不重要。

Looking for the solution that requires less computational power Excel or Excel-VBA. The final order which they appear in sheet2 don't really matter.

推荐答案

对Recorder生成的代码使用轻微修改： / p>

Using a slight modification to Recorder-generated code:

Sub Macro1()
    Sheets("Sheet1").Range("A:A").Copy Sheets("Sheet2").Range("A1")
    Sheets("Sheet2").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
        With Sheets("Sheet2").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A" & Rows.Count) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A2:A" & Rows.Count)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

样本 Sheet1 ：

样本 Sheet2 ：

排序删除空白。

编辑＃1：

如果原始数据 Sheet1 是从公式导出的，那么使用PasteSpecial将删除不需要的公式复制。还有一个空单元格的最后一个扫描：

If the original data in Sheet1 was derived from formulas, then using PasteSpecial will remove unwanted formula copying. There is also a final sweep for empty cells:

Sub Macro1_The_Sequel()
    Dim rng As Range

    Sheets("Sheet1").Range("A:A").Copy
    Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
    Sheets("Sheet2").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
    Set rng = Sheets("Sheet2").Range("A2:A" & Rows.Count)
    With Sheets("Sheet2").Sort
        .SortFields.Clear
        .SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rng
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Call Kleanup
End Sub

Sub Kleanup()
    Dim N As Long, i As Long

    With Sheets("Sheet2")
        N = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = N To 1 Step -1
            If .Cells(i, "A").Value = "" Then
                .Cells(i, "A").Delete shift:=xlUp
            End If
        Next i
    End With
End Sub

这篇关于来自动态范围表的唯一列表与可能的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！