CommandButton Macro to hide row

[darrylbster]

Good afternoon all.
I've been playing with VBA, brand new to it and having fun, but I can't figure something out...naturally.

I want to use a commandbutton to run a Macro, seelct the row of the current cell, copy it, jump to the first available blank row, paste contents.

This is what I have...

Private Sub CommandButton1_Click()

'Step1: select the erroneous row and copt the contents
​

ActiveCell.EntireRow.Copy​

'Step2: Erroneous the erroneous row
​

Range(ActiveCell.EntireRow).Hidden
​

'Step3: Jump to the first available blank cell and paste contents from erronaous cell
​

Range("A" & Rows.Count).End(xlUp).Offset(1).Select​

'Step4: Paste contents of Erroneous row
​

Paste
​

End Sub

If I remove step two it does what I was expecting, no issues. AS soon as I add step two I get a Compile Error, invalid use of property.
I was getting the same kind of issue jumping rows, I was initially trying "ActiveCell.EntireRow.Offset(1, 0).Cells(1).EntireRow.Select" and it did not work, once I reworded it I found positive results. It seems as is nothig I'm doing is changing the fact that .Hidden is invalid.

I am assuming there is a work around?

 

[Rick Rothstein]

Hidden is a property that must be assigned a value... assign True to hide the range or assign False to unhide the range.

A tip that may help you... you can click into any keyword (for example, Hidden) in an code line so that the text cursor is in and immediately next to that keyword and then press the F1 key to bring up the help file for that keyword. Besides telling you the syntax for using the keyword, usually there is an example to help guide you.

 

[darrylbster]

Hidden is a property that must be assigned a value... assign True to hide the range or assign False to unhide the range.

A tip that may help you... you can click into any keyword (for example, Hidden) in an code line so that the text cursor is in and immediately next to that keyword and then press the F1 key to bring up the help file for that keyword. Besides telling you the syntax for using the keyword, usually there is an example to help guide you.

Thanks for the tip regading F1, and your help understanding the .hidden command. I was able to get my Macro to work with an extra line.

Private Sub CommandButton1_Click()

'Step1: select the erroneous row and copt the contents
​

ActiveCell.EntireRow.Copy​

'Step2: Erroneous the erroneous row​

Range(ActiveCell.EntireRow).Hidden = True
​

'Step3: Jump to the first available blank cell and paste contents from erronaous cell
​

Range("A" & Rows.Count).End(xlUp).Offset(1).Select​

'Step4: Paste contents of Erroneous row
​

Paste
​

'Step5: Step 4 will paste the erraneous row as hidden, unhide the row
​

ActiveCell.EntireRow.Hidden = False
​

End Sub

That's my first ever Macro down, now to modify it to select only unprotected columns across the row.

Thanks for the help!

 

[darrylbster]

I thought I would post the finished Macro for any other rookie looking to do something similar.

Private Sub CommandButton1_Click()

'Step1: Unprotect the sheet so changes can be made
​

Sheet1.Unprotect Password:="yourpassword"
​

'Step2: select the erroneous row and copt the contents
​

ActiveCell.EntireRow.Copy​

'Step3: hide the erroneous row and copt the contents
​

ActiveCell.EntireRow.Hidden = True​

'Step4: Jump to the first available blank cell and paste contents from erronaous cell
​

Range("A" & Rows.Count).End(xlUp).Offset(1).Select​

'Step5: Paste contents of Erroneous row
​

Paste​

'Step6: Step 4 will paste the erraneous row as hidden, unhide the row
​

ActiveCell.EntireRow.Hidden = False
​

'Step7: Step 4 Deselect All
​

SendKeys "{ESC}"
​

'Step8: Re-Protect sheet to prevent Jack-Assery
​

Sheet1.Protect Password:="yourpassword"
​

End Sub