0

I am using VBA for Excel 2016 and keep getting the following error:

Run-time error '1004':
Application-defined or object-defined error

Here is my code:

Sub Macro2()

Dim wbk As Workbook
Dim countermax As Integer
Dim ws As String
Dim i As Integer 'for counter
Dim x As Integer 'for serial number of month
Dim m As Long
Dim macro As Worksheet

Set wbk = ThisWorkbook
Set macro = wbk.Worksheets("Macro")
countermax = wbk.Worksheets("macro").Range("A1")
i = 2
x = wbk.Worksheets("macro").Range("D2")
y = 12 - x
z = y - 1

Do While i < countermax
    ws = wbk.Worksheets("macro").Range("B" & i)
    **m = wbk.Worksheets(ws).Range(Range("A9").End(xlDown), Range("A9").End(xlDown).End(xlDown)).Rows.Count**

    Worksheets(ws).Range(Range("A9").End(xlDown), Range("A9").End(xlDown).Offset(m, 0)).EntireRow.Select
    Selection.Rows.Hidden = False
    On Error Resume Next
    Selection.Rows.Ungroup

   If y <> 0 Then
        Range("A9").Select
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Range(Range("A9").End(xlDown), Range("A9").End(xlDown).Offset(-z, 0)).EntireRow.Select
        Selection.Rows.Group
        Selection.Rows.Hidden = True
    Else: End If

    i = i + 1

Loop

End Sub

Debugging this code highlights the line in **.

Every month I have to manually go through each tab in a workbook and ungroup + unhide the next hidden row in a particular range that represents the months of the year. What I'd like to do is have a macro go through every worksheet that has its name listed in the macro worksheet and unhide the next month's row. Because not every worksheet has the same number of rows (e.g. the first month may be January for one worksheet, March for another), I'm trying to have Excel count the number of rows and store that as a data type so it knows how many to hide.

When I hover my cursor over the "m" variable, it tells me "m = 0". Can someone please explain to me why it's not giving me the row count?

Community
  • 1
  • 1
PLH
  • 1
  • 1
  • I can't figure out how to more clearly point out the line that's giving me an error so I apologize if it's hard to find in my code – PLH Sep 28 '16 at 02:05
  • Consider changing the way you find the number of rows (m), e.g. http://stackoverflow.com/questions/6301665/how-to-count-the-number-of-rows-in-excel-with-data – NoChance Sep 28 '16 at 02:12
  • The problem is that all ranges within a range must reference the same worksheet: `wbk.Worksheets(ws).Range(Range("A9").End(xlDown), Range("A9").End(xlDown).End(xlDown)).Rows.Count` . wbk.Worksheets(ws).Range()` refers to cells on `wbk.Worksheets(ws)` and `Range("A9").End(xlDown), Range("A9").End(xlDown).End(xlDown)` refers to cells on the ActiveSheet. –  Sep 28 '16 at 02:16
  • Do you _reaaallllyyyy_ have to use [.Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)??? – Tyeler Sep 28 '16 at 03:28
  • I looked at the duplicate question marked by chris neilsen and reasoned out a solution. Also thanks to @Thomas for your explanation as well. – PLH Oct 03 '16 at 20:54
  • @Tyeler I updated my code to remove select. Thanks for that added challenge ;) – PLH Oct 03 '16 at 20:56
  • @PLH No problem! It's usually best, for your code and mental health, to avoid using `.Select` as much as possible. It often provides non-intuitive results that could lead you to troubleshooting the wrong parts of your code. I'm glad you got it all working! – Tyeler Oct 04 '16 at 01:24

0 Answers0