Activate worksheet using excel VBA

Question

I have a file "Workbook A" in a folder. An updated version is sent to me every fortnight. I want to open this workbook from another workbook, "Workbook B" and at the same time delete blank rows in "Workbook A".

The opening and deleting operations will occur through the use of a macro.

This is my code thus far.

Sub RemoveEmptyRows()
    ' this macro will remove all rows that contain no data
    ' ive named 2 variables of data type string
    Dim file_name  As String
    Dim sheet_name As String

    file_name = "C:\Users\Desktop\Workstation_A\Workbook_A.xlsm"
    'Change to whatever file i want
    sheet_name = "Worksheet_A"   'Change to whatever sheet i want

    ' variables "i" and "LastRow" are needed for the for loop
    Dim i As Long
    Dim LastRow As Long

    ' we set wb as a new work book since we have to open it
    Dim wb As New Workbook

    ' To open and activate workbook
    ' it opens and activates the workbook_A and activates the worksheet_A
    Set wb = Application.Workbooks.Open(file_name)
    wb.Sheets(sheet_name).Activate

    ' (xlCellTypeLastCell).Row is used to find the last cell of the last row
    ' i have also turned off screen updating
    LastRow = wb.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    Application.ScreenUpdating = False

    ' here i am using a step
    ' the step is negative
    ' therefore i start from the last row and go to the 1st in steps of 1
    For i = LastRow To 1 Step -1
    ' Count A - Counts the number of cells that are not empty and the
    ' values within the list of arguments (wb..ActiveSheet.Rows(i))
    ' Afterwards deleting the rows that are totally blank
        If WorksheetFunction.CountA(wb.ActiveSheet.Rows(i)) = 0 Then
            wb.ActiveSheet.Rows(i).EntireRow.Delete
        End If
    Next i

    ' used to update screen
    Application.ScreenUpdating = True
End Sub

The work sheet name contains Worksheet_A as part of its name followed by a date. For example Worksheet_A 11-2-15.

In my code, I have assigned the variable sheet_name to Worksheet_A

sheet_name = "Worksheet_A"

and further down I have used

.Sheets(sheet_name).Activate

to activate the worksheet. I feel there is an issue with below line:

sheet_name = "Worksheet_A"

since sheet_name is not exactly Worksheet_A it only contains Worksheet_A as part of its name.

This is causing a problem.The workbook A opens but the deleting of blank rows does not occur.
Further more an error message is displayed.

Run Time Error 9: Subscript out of Range.

How do I modify my code so that the worksheet gets activated and the macro operations are performed?

L42 L42 · Accepted Answer · 2015-02-23T05:22:22

is it possible to solve this by using Like or Contain statements?
From your comment, yes. After opening the workbook, iterate the worksheet collection like this:

Dim sh As Worksheet
For Each sh In wb.Sheets
    If InStr(sh.Name, "WorkSheet_A") <> 0 Then
        sheet_Name = sh.Name: Exit For
    End If
Next

Or you can grab that object and work directly on it.

Dim sh As Worksheet, mysh As Worksheet
For Each sh In wb.Sheets
    If InStr(sh.Name, "WorkSheet_A") <> 0 Then
        Set mysh = sh: Exit For
    End If
Next

LastRow = mysh.Cells.SpecialCells(xlCellTypeLastCell).Row
'~~> rest of your code here

If you only have one(1) Worksheet though, you can access it through index.

Set mysh = wb.Sheets(1)

You might find this POST interesting which discusses how to avoid Select/Activate/Active to further improve your coding in the future. HTH.

Here is your code tailored:

Sub RemoveEmptyRows()
    Dim file_name  As String

    file_name = "C:\Users\Desktop\Workstation_A\Workbook_A.xlsm"
    Dim i As Long
    Dim LastRow As Long

    Dim wb As Workbook, mysh As Worksheet
    Set wb = Application.Workbooks.Open(file_name)
    'Above code is same as yours

    Set mysh = wb.Sheets(1) 'if only one sheet, use loop otherwise

    Application.ScreenUpdating = False
    Dim rngtodelete As Range
    With mysh
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        'Collect all the range for deletion first
        For i = LastRow To 1 Step -1
            If WorksheetFunction.CountA(.Rows(i)) = 0 Then
                If rngtodelete Is Nothing Then
                    Set rngtodelete = .Rows(i)
                Else
                    Set rngtodelete = Union(rngtodelete, .Rows(i))
                End If
            End If
        Next i
    End With
    'Delete in one go
    If Not rngtodelete Is Nothing Then rngtodelete.Delete xlUp
    Application.ScreenUpdating = True
End Sub

Activate worksheet using excel VBA

2 Answers