vba runtime error 1004 application defined or object defined error

[Maddy203]

Hi all,

I am new to excel and trying to write VBA code for a macro. I am trying to hide certain set of rows but getting an "vba runtime error 1004 application defined or object defined error".

My code is:

Sub Rowhide()

For j = 1 To Range("AW11").Value

Rows(j).EntireRow.Hidden = False

Next j

For i = Range("AW11").Value To 7656

Rows(i).EntireRow.Hidden = True

Next i

End Sub

In debugging, it is highlighting following row

Rows(i).EntireRow.Hidden = True

P.S: I have assigned this macro to a button in a different sheet.

Kindly help me resolve this error. Any help is appreciated. TIA

Mod Edit: Code tags added

 

[Debaser]

Is AW11 on the button's sheet empty?

 

[NARAYANK991]

Hi ,

In addition to the question which has already been asked , which can generate the error you mention , the issue is :

1. Where is this code placed ?

2. Which sheet is it operating on i.e. on which sheet are the rows being unhidden and hidden ?

3. Which sheet has a value in cell AW11 ?

If you assign this macro to a button any particular sheet , it follows that to click on the button , that particular sheet has to be the active sheet. If the code does not qualify the references , all references will be with respect to the active sheet. This can mean that the rows which contain the button can also be hidden depending on where the button is placed , and what the value in cell AW11 is.

Narayan

 

[Maddy203]

Is AW11 on the button's sheet empty?

Hi Debaser,
Thanks for your prompt reply!

Yes the AW11 cell is empty in the sheet with the button. What should be ideally done in such cases?

TIA

 

[Maddy203]

Hi ,

In addition to the question which has already been asked , which can generate the error you mention , the issue is :

1. Where is this code placed ?

2. Which sheet is it operating on i.e. on which sheet are the rows being unhidden and hidden ?

3. Which sheet has a value in cell AW11 ?

If you assign this macro to a button any particular sheet , it follows that to click on the button , that particular sheet has to be the active sheet. If the code does not qualify the references , all references will be with respect to the active sheet. This can mean that the rows which contain the button can also be hidden depending on where the button is placed , and what the value in cell AW11 is.

Narayan

Hi Narayan,

Thanks for your prompt response.

My code is in Module 1, intended for Sheet1 named 'Input draft'. The rows have to be hidden or unhidden in this sheet. And the AW11 cell too is from this sheet.

The button however is in Sheet3 named 'Front draft'.

So kindly help me rectifying my code.

 

[Debaser]

You should specify the sheet:

Sub Rowhide()
With Sheets("Input draft")

.Range("1:" & .Range("AW11").Value - 1).EntireRow.Hidden = False
.Range(.Range("AW11").Value & ":7656").EntireRow.Hidden = True
End With
End Sub

Note there was an overlap in your rows, so I guessed which one to subtract 1 from.

 

[NARAYANK991]

Hi ,

Try this :

Sub Rowhide()
    Const THRESHOLDROWNUM = 20
    Dim Outputwks As Worksheet
    Dim AW11val As Long
   
    Set Outputwks = ThisWorkbook.Worksheets("Input draft")
   
    With Outputwks
        .Range("A1").Resize(7656).EntireRow.Hidden = False
           
        AW11val = IIf(.Range("AW11").Value < THRESHOLDROWNUM, THRESHOLDROWNUM, .Range("AW11").Value)

        .Range("A" & AW11val, "A" & 7656).EntireRow.Hidden = True
    End With
End Sub

Narayan

 

[Maddy203]

You should specify the sheet:

Sub Rowhide()
With Sheets("Input draft")

.Range("1:" & .Range("AW11").Value - 1).EntireRow.Hidden = False
.Range(.Range("AW11").Value & ":7656").EntireRow.Hidden = True
End With
End Sub

Note there was an overlap in your rows, so I guessed which one to subtract 1 from.

Thanks a ton Debaser, your code works as a charm!!

 

[Maddy203]

Hi ,

Try this :

Sub Rowhide()
    Const THRESHOLDROWNUM = 20
    Dim Outputwks As Worksheet
    Dim AW11val As Long
  
    Set Outputwks = ThisWorkbook.Worksheets("Input draft")
  
    With Outputwks
        .Range("A1").Resize(7656).EntireRow.Hidden = False
          
        AW11val = IIf(.Range("AW11").Value < THRESHOLDROWNUM, THRESHOLDROWNUM, .Range("AW11").Value)

        .Range("A" & AW11val, "A" & 7656).EntireRow.Hidden = True
    End With
End Sub

Narayan

Hi Narayan,

Thanks for your help. Your code works great. However could you please explain the logic that you've used. Sorry, I am just a complete novice in excel and VBA.

 

[NARAYANK991]

Hi ,

It is not really different from that posted by Debaser.

1. .Range("A1").Resize(7656).EntireRow.Hidden = False

This line unhides all the rows from row 1 through row 7656. Check out the Excel VBA help on Resize.

2. AW11val = IIf(.Range("AW11").Value < THRESHOLDROWNUM, THRESHOLDROWNUM, .Range("AW11").Value)

This is merely to ensure that if you leave the cell AW11 empty , or enter a value lesser than a pre-defined value of 20 ( I have used the constant named THRESHOLDNUM to hold this value ) , then this value is used as the start row.

This is just to ensure that if AW11 is empty , it does not throw an error , and if by mistake AW11 contains a value less than 12 , then even this row will be hidden , and in order to get at the cell will be time-consuming since row 11 will have to be unhidden.

3. .Range("A" & AW11val, "A" & 7656).EntireRow.Hidden = True

This line hides all the rows from the value entered in cell AW11 till row 7656.

Narayan