Excel VBA - 빈 행 삭제

Excel VBA - 빈 행 삭제

제 ERP Quotation에서 생성한 빈 행을 삭제하고 싶습니다.서류를 훑어보려고 합니다.A1:Z50) 및 셀에 데이터가 없는 각 행에 대해 (A1-B1...Z1 = empty,A5-B5...Z5 = empty삭제하고 싶습니다.

이것을 찾았지만 구성할 수 없습니다.

On Error Resume Next
Worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

어때.

sub foo()
  dim r As Range, rows As Long, i As Long
  Set r = ActiveSheet.Range("A1:Z50")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next
End Sub

사용해 보세요.

Option Explicit

Sub Sample()
    Dim i As Long
    Dim DelRange As Range

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    For i = 1 To 50
        If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "Z" & i)) = 0 Then
            If DelRange Is Nothing Then
                Set DelRange = Range("A" & i & ":" & "Z" & i)
            Else
                Set DelRange = Union(DelRange, Range("A" & i & ":" & "Z" & i))
            End If
        End If
    Next i

    If Not DelRange Is Nothing Then DelRange.Delete shift:=xlUp
LetsContinue:
    Application.ScreenUpdating = True

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

전체 행을 삭제하려면 이 코드를 사용합니다.

Option Explicit

Sub Sample()
    Dim i As Long
    Dim DelRange As Range

    On Error GoTo Whoa

    Application.ScreenUpdating = False

    For i = 1 To 50
        If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "Z" & i)) = 0 Then
            If DelRange Is Nothing Then
                Set DelRange = Rows(i)
            Else
                Set DelRange = Union(DelRange, Rows(i))
            End If
        End If
    Next i

    If Not DelRange Is Nothing Then DelRange.Delete shift:=xlUp
LetsContinue:
    Application.ScreenUpdating = True

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

제가 파티에 늦었다는 것은 알지만, 여기 제가 그 일을 하기 위해 작성한 코드가 있습니다.

Sub DeleteERows()
    Sheets("Sheet1").Select
    Range("a2:A15000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

빈 셀이 하나도 없는 행만 삭제하고 빈 셀은 삭제하지 않으려는 경우를 위해 다른 행이 있습니다.Access-VBA 또는 VB6를 통해 Excel에 액세스하는 등 Excel 외부에서도 작동합니다.

Public Sub DeleteEmptyRows(Sheet As Excel.Worksheet)
    Dim Row As Range
    Dim Index As Long
    Dim Count As Long

    If Sheet Is Nothing Then Exit Sub

    ' We are iterating across a collection where we delete elements on the way.
    ' So its safe to iterate from the end to the beginning to avoid index confusion.
    For Index = Sheet.UsedRange.Rows.Count To 1 Step -1
        Set Row = Sheet.UsedRange.Rows(Index)

        ' This construct is necessary because SpecialCells(xlCellTypeBlanks)
        ' always throws runtime errors if it doesn't find any empty cell.
        Count = 0
        On Error Resume Next
        Count = Row.SpecialCells(xlCellTypeBlanks).Count
        On Error GoTo 0

        If Count = Row.Cells.Count Then Row.Delete xlUp
    Next
End Sub

빈 행과 빈 행(Ctrl + Shift + 워크시트의 깊은 곳으로 이동)을 제거하려는 사용자를 위해 .여기 제 코드가 있습니다.각 시트에서 마지막 "실제" 행을 찾고 나머지 빈 행을 삭제합니다.

Function XLBlank()
    For Each sh In ActiveWorkbook.Worksheets
        sh.Activate
        Cells(1, 1).Select
        lRow = Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
        
        Range("A" & lRow + 1, Range("A1").SpecialCells(xlCellTypeLastCell).Address).Select
        On Error Resume Next
        Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
        Cells(1, 1).Select
    Next
    ActiveWorkbook.Save
    ActiveWorkbook.Worksheets(1).Activate
End Function

VBA(ALT + F11)를 열고 -> 모듈을 삽입한 후 내 코드를 복사하여 F5로 실행합니다.에트빌라:d

다음은 빈 행을 모두 삭제하는 가장 빠른 방법입니다(한 열 기준).

Dim lstRow as integet, ws as worksheet

Set ws = ThisWorkbook.Sheets("NameOfSheet")

With ws

     lstRow = .Cells(Rows.Count, "B").End(xlUp).Row ' Or Rows.Count "B", "C" or "A" depends 

     .Range("A1:E" & lstRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End with

Alex K의 답변을 조금 더 역동적으로 만들기 위해 아래 코드를 사용할 수 있습니다.

Sub DeleteBlankRows()

Dim wks As Worksheet
Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
    lngColCounter As Long
Dim blnAllBlank As Boolean
Dim UserInputSheet As String

UserInputSheet = Application.InputBox("Enter the name of the sheet which you wish to remove empty rows from")

Set wks = Worksheets(UserInputSheet)

With wks
    'Now that our sheet is defined, we'll find the last row and last column
    lngLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlPrevious).Row
    lngLastCol = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                             SearchOrder:=xlByColumns, _
                             SearchDirection:=xlPrevious).Column

    'Since we need to delete rows, we start from the bottom and move up
    For lngIdx = lngLastRow To 1 Step -1

        'Start by setting a flag to immediately stop checking
        'if a cell is NOT blank and initializing the column counter
        blnAllBlank = True
        lngColCounter = 2

        'Check cells from left to right while the flag is True
        'and the we are within the farthest-right column
        While blnAllBlank And lngColCounter <= lngLastCol

            'If the cell is NOT blank, trip the flag and exit the loop
            If .Cells(lngIdx, lngColCounter) <> "" Then
                blnAllBlank = False
            Else
                lngColCounter = lngColCounter + 1
            End If

        Wend

        'Delete the row if the blnBlank variable is True
        If blnAllBlank Then
            .rows(lngIdx).delete
        End If

    Next lngIdx
End With


MsgBox "Blank rows have been deleted."

 End Sub

이것은 이 웹 사이트에서 가져온 것으로, 사용자가 행을 비울 워크시트를 선택할 수 있도록 약간 조정되었습니다.

오류 발생 시 재개 기능이 작동하려면 워크북 및 워크시트 값을 다음과 같이 선언해야 합니다.

On Error Resume Next  
ActiveWorkbook.Worksheets("Sheet Name").Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete  
On Error GoTo 0

저도 같은 문제가 있었고, 이로 인해 For 루프를 구현할 필요 없이 빈 행이 모두 제거되었습니다.

이것은 저에게 매우 효과적이었습니다(필요에 따라 마지막 행과 마지막 색상을 조정할 수 있습니다).

Sub delete_rows_blank2()

t = 1
lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count

Do Until t = lastrow

For j = 1 To lastcol
    'This only checks the first column because the "Else" statement below will skip to the next row if the first column has content.
    If Cells(t, j) = "" Then

        j = j + 1

            If j = lastcol Then
            Rows(t).Delete
            t = t + 1
            End If

    Else
    'Note that doing this row skip, may prevent user from checking other columns for blanks.
        t = t + 1

    End If

Next

Loop

End Sub

언급URL : https://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows