>source

VBA for Excel에서 여러 기준에 따라받는 .xlsx 파일을 분석하는 도구를 만들었습니다. 그중 하나는 데이터 세트의 빈 셀 수입니다. 그러나 불행하게도, 우리가받는 많은 파일들은 길이가 0 인 문자열을 가진 셀을 포함하고 있으며 비어 있지 않은 셀로 "정확하지 않은"것으로 계산됩니다.

파일에서 이것들을 제거 할 수 있다면 실제로 전체 프로세스에 큰 도움이됩니다.

이 문제를 많이 봤지만 지금까지 찾은 유일한 해결책은 시트의 모든 셀을 반복하는 것입니다 (상수를 시도했지만 찾기를 사용하여 모든 zl을 찾습니다) . 워크 시트에 많은 양의 데이터가 있기 때문에 이것은 효율적이지 않습니다.편집 : UsedRange.values ​​= UsedRange.values ​​메서드도 시도했지만 필요한 0을 제거합니다.

또한 이것이 작동한다는 것을 발견했습니다 (-@-는 데이터에서 단일 셀이 될 가능성이 거의없는 임의의 문자열이며, 존재하는 경우 제거해도 좋습니다).

ws.UsedRange.Replace what:=vbNullString, replacement:="-@-", _
                            lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what:="-@-", replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

단일 교체 만 사용하여 수행하는 경우에는 그렇지 않습니다 :

ws.UsedRange.Replace what:=vbNullString, replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

첫 번째는 괜찮지 만 여러 가지 문제가 있습니다 :

  1. 한 번의 교체보다 두 배가 걸립니다
  2. 충돌했거나 계속 작동했는지 항상 명확하지는 않습니다
  3. 충돌이 발생하면-@-가 포함 된 수많은 셀이 남았는데, 항상 명확한 것은 아니며 VBA를 이해할 수없는 사람이 사용할 수 있도록 도구를 사용해야합니다.

내 질문 :

  1. 단일 교체를 사용하여이 작업을 수행하는 방법이 있습니까? 교체가 이중 교체로 작동하지만 단일 교체로 작동하지 않는 이유는 무엇입니까?
  2. 첫 번째 방법이 가능하지 않은 경우 코드 충돌시 대체를 '롤백'하는 방법이 있습니까?
  3. Excel 자체에서 replace를 실행할 때와 같이 코드가 실행되고 있음을 증명하기 위해 replace를 통해 얼마나 멀리 있는지 표시하기 위해 상태 표시 줄을 업데이트하는 방법이 있습니까?
  4. 또는 더 좋은 방법이 있습니까?

미리 감사합니다!

수정 : 불행히도, 데이터가 처리되고 있기 때문에 선행 0을 포함한 서식을 유지해야합니다

편집 : 여기 내가보고있는 데이터 종류의 예가 있습니다. 길이가 0 인 문자열 (값이없는 빈 셀이 아닌 셀)을 바꾸려고하므로 진정한 빈 셀이됩니다.


  • 답변 # 1

    이것은 루프이지만 더 빠른 방법이라는 것을 알고 있습니다 :

    이전 :

    샘플 데이터에서이 코드를 실행하십시오 :

    Dim X As Double
    Option Explicit
    Sub Test()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    With ActiveWorkbook.Sheets(1).Range("A1:C7")
        For X = 1 To 3
            .AutoFilter Field:=X, Criteria1:=""
            .Columns(X).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
        Next X
        .AutoFilter
    End With
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    End Sub
    
    

    이후 :

  • 답변 # 2

    null 문자열 또는 null 문자열 상수를 가진 셀 두 번째 종류의 셀을 지우려면 :

    Sub KillNullConstants()
        Dim cell As Range, Konstants As Range, rng As Range
        Set Konstants = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants)
        Set rng = Nothing
        For Each cell In Konstants
            If Len(cell) = 0 Then
                If rng Is Nothing Then
                    Set rng = cell
                Else
                    Set rng = Union(rng, cell)
                End If
            End If
        Next cell
        If Not rng Is Nothing Then
            rng.ClearContents
        End If
    End Sub
    
    
    null 문자열을 반환하는 수식 셀을 지우려면 SpecialCells 를 변경하십시오.  코드 행.

  • 답변 # 3

    시트를 ADO 레코드 세트로 읽은 다음 레코드 세트를 새 시트로 복사하면이 문제가 해결되는 것 같습니다. 이 VBA 코드를 사용해보십시오 :

    Sub copy_data()
    Dim cn As Object
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
        .Open
    End With
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [Sheet1$];", cn
    Dim i As Integer
    Dim fld As Object
    With ThisWorkbook.Worksheets("Sheet2")
        .UsedRange.ClearContents
        i = 0
        For Each fld In rs.Fields
            i = i + 1
            .Cells(1, i).Value = fld.Name
        Next fld
        .Cells(2, 1).CopyFromRecordset rs
        .UsedRange.Columns.AutoFit
    End With
    rs.Close
    cn.Close
    End Sub
    
    

    참고 사항 :

    현재 시트 이름과 일치하도록 코드에서 시트 이름을 변경하십시오. 사용. 당신은 $ 를 넣어야  사용하는 시트 이름 뒤에 서명 와이즈 비즈 . 시트가 "데이터"라고한다면 와이즈 비즈

    연결 문자열 확장 특성 rs.Open 에서

    데이터가 헤더. ADO에 포함 된 경우 일부 열 이름이 변경 될 수 있습니다. 특정 문자-주로 rs.Open "SELECT * FROM [Data$];", cn  일반적으로 문자 HDR=YES 로 대체되다  캐릭터

    잠재적 문제 :

    데이터는 대략 테이블 형식 (예 : 열 이름)이어야합니다. 각 열 이름 아래에 데이터 값이있는 행 1에서

    데이터의 모든 수식이 값으로 변환됩니다

    셀 서식은 복사되지 않고 숫자로 복사됩니다. 텍스트 형식은 앞에 오는 0이 유지되는 텍스트

    255 자보다 긴 셀 값 텍스트는 잘릴 수 있습니다. 255 자 (필요한 경우이 문제를 해결할 수 있음)

    데이터에 따라 ADO를 사용하면 해결하는 것보다 더 많은 문제가 발생할 수 있습니다

    .

관련 자료

  • 이전 javascript - 엔터티없이 JSON 배열에서 값을 읽습니다
  • 다음 python - typeerror : iterable에만 참여할 수 있습니다