CODEWITHSUNDEEP
excelexcel-2010vba
vbaNovice
vbaNovice

Reputation: 11

Excel 2010, VBA From Different Workbook Cannot Return IsEmpty Value

The below code should delete a row if Cell(1,2) is empty. When being run within the workbook which contains the code, it accurately returns the IsEmpty function. When being run from an ActiveSheet in another workbook, it will not return the IsEmpty function. Is there a solution for this?

Public Sub formatSheet()
Dim test As Boolean
test = IsEmpty(ActiveSheet.Cells(1, 2))
'Removes header lines
Do Until test = False
    Rows("1:1").Delete
    test = IsEmpty(ActiveSheet.Cells(1, 2))
Loop
End Sub

Upvotes: 0

Views: 68

Answers (2)

Daniel
Daniel

Reputation: 16

You don't need to activate the spreadsheet you want it to run on, but you do need to refer to the sheet and workbook. To do this without creating objects and associated pointers, try this code:

    test = IsEmpty(Workbook("NameOfWorkBookYouWishToCheck").Sheets("NameOfSheetYouWishToCheck").Cells(1, 2))

Upvotes: 0

FruitUser
FruitUser

Reputation: 115

You need to activate the workbook and worksheet you want to delete the row from first:

How do i activate a specific workbook and a specific sheet?

Hope that link helps!

Upvotes: 0

Related Questions