Activate worksheet using excel VBA
excel, vba
Solution
`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
Problem
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?