Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Select worksheet in VB for Excel not working

Experts,

I am tryhing to selet a worksheet so I can delete rows when the values of all of the columns is 0. I am getting an error on the select statement.  I am passing the string in the call to the routine.  Doesanyone see what the problem is?


****Call
Call HospitalCleanup("MEDICAL_VOID_REBUILD")



***routine

Sub HospitalCleanup(Product As String)
Dim lastrow As Long, i As Long, j As Long 'define variables
Application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes
ActiveSheet.Unprotect
Sheets(Product).Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row 'get last row

For i = lastrow To 9 Step -1
    If Len(Trim(Cells(i, 1))) > 0 Then
        For j = 1 To 12
            If Cells(i + 2, j * 2 + 1) <> 0 And Len(Cells(i + 2, j * 2 + 1)) > 0 Then GoTo notDelete
        Next
           
        Rows(i).Resize(4).Delete
notDelete:
    End If
Next i
    With Worksheets(Product)
       .Activate
       .Range("B5") = Product
       .Range("G3") = ""
    End With

ActiveSheet.Protect
Sheets(Product).Visible = False
Application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub
0
morinia
Asked:
morinia
2 Solutions
 
MacroShadowCommented:
Should be:
Sheets("Product").Select

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Are you certain that MEDICAL_VOID_REBUILD is the name of the sheet?

Also what error are you getting?
0
 
MacroShadowCommented:
Oops I was too quick to jump the gun...
The code looks ok (workable...) if the sheet MEDICAL_VOID_REBUILD exists I don't see a problem.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
moriniaAdvanced Analytics AnalystAuthor Commented:
Experts,

The sheet is "MEDICAL_VOID_REBUILD ".  I think the problem may have been the sheet was hidden. Does that sound correct. I made the sheet visible and I did not see the error.
0
 
Martin LissRetired ProgrammerCommented:
Yes it does and I should have thought of that since you can't Activate or Select a hidden sheet.
0
 
Martin LissRetired ProgrammerCommented:
Thanks for the points but there's no need to assign them when you self-resolve a problem. If you 'Request Attention' I won't have any objection if you ask the moderator to help you remove the point assignment.
0
 
NorieData ProcessorCommented:
I know this is solved but you should be able to do what you want without selecting/activating, or making the sheet visible.
Sub HospitalCleanup(Product As String)
Dim lastrow As Long, i As Long, j As Long    'define variables

    Application.ScreenUpdating = False    'disable ScreenUpdating to avoid screen flashes

    With Sheets(Product)
        .Unprotect
        lastrow = .Cells(Rows.Count, "A").End(xlUp).Row    'get last row

        For i = lastrow To 9 Step -1
            If Len(Trim(.Cells(i, 1))) > 0 Then
                For j = 1 To 12
                    If .Cells(i + 2, j * 2 + 1) <> 0 And Len(.Cells(i + 2, j * 2 + 1)) > 0 Then GoTo notDelete
                Next j

                .Rows(i).Resize(4).Delete
notDelete:
            End If
        Next i

        .Range("B5") = Product
        .Range("G3") = ""

        .Protect
    End With
    
    Sheets(Product).Visible = False
    Application.ScreenUpdating = True    'reenable ScreenUpdating
End Sub

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now