CODEWITHSUNDEEP
excelvba
Alexey
Alexey

Reputation: 23

Deleting empty rows on different workbook

I have a little bit a problem, which I can't figure out. (It seems to be pretty simple taking into account functions used).

Short description: I have a code that enters file and copy some columns from there to DestWbk, sheets' name is "sheet1". This is just a part of the code, but the data is copied and now I need to clear it from rows with empty values in specific column.

DestWbk=ThisWorkbook

        check = "alpha"
        checkcol = DestWbk.Sheets("sheet1").Cells.Find(What:=check, LookIn:=xlValues).Column
        MsgBox (checkcol)
           Set column_range = DestWbk.Sheets("sheet1").Cells(checkcol).EntireColumn

            If DestWbk.Worksheets("sheet1").WorksheetFunction.CountBlank(DestWbk.Worksheets("sheet1").column_range) > 0 Then
            DestWbk.Worksheets("sheet1").column_range.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            End If

Upvotes: 1

Views: 49

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Something like this should work:

Dim f as range, sht1 as worksheet, column_range as range, check

Set sht1 = DestWbk.Sheets("sheet1")

check = "alpha"
Set f= sht1.Cells.Find(What:=check, LookIn:=xlValues)

'make sure you got a match from Find before proceeding
if not f is nothing then
    Set column_range = sht1.usedrange.columns(f.column)
    on error resume next 'ignore error if no blanks
    column_range.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    on error goto 0      'stop ignoring errors
End If

Upvotes: 3

Related Questions