VBA Macro has begun hiding rows instead of copying down.

Steady

New Member
Joined
Jun 22, 2019
Messages
8
Hello all,

Can anyone help with the issue i am currently having on my excel spreadsheet? The below code was working perfectly fine but when i close it and re-open the file it starts causing errors. The code is designed to search a large list on another sheet and if found copy the entire row to the current sheet.

For some reason now it's copying the rows but hiding most of them and then deleting them. You can see in the attached snapshot that row 15 is hidden, then it deletes everything after that row it finds (There should be 79 rows) I've never had this problem before and cannot seem to fix it, any help would be greatly appreciated.


Sub consumer_Click()

Range("A2:D300").ClearContents


a = Worksheets("All Ords").Cells(Rows.Count, 1).End(xlUp).row

For i = 2 To a

If Worksheets("All Ords").Cells(i, 3).Value = "Consumer Discretionary" Then

Worksheets("All Ords").Rows(i).Columns("A:D").Copy

Worksheets("Consumer Discretionary").Activate

b = Worksheets("Consumer Discretionary").Cells(Rows.Count, 1).End(xlUp).row

Worksheets("Consumer Discretionary").Cells(b + 1, 1).Select

ActiveSheet.Paste

End If

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Consumer Discretionary").Cells(1, 1).Select

Next


End Sub
 
Last edited by a moderator:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
This script if you can read it and understand it does not hide anything and deletes nothing.

Now it does clear the contents of:
Range("A2:D300").ClearContents

Show me in the script where you see Hide or delete?


So you may think it's hiding and deleting but there is nothing in this code that says Hide or delete
 
Upvote 0
Oh sorry it seems the photo i attached was deleted by the mods.
I know the code doesn't contain any hide function thats why i'm so confused. But yet when i run this now it'll copy a bunch of rows then it hides a bunch of rows and starts deleting any after that. Any ideas what could be causing that?
 
Upvote 0
can you right click on your worksheet tab where it shows the name, and click view code, and check if there is any code in it? (do for both worksheets)
 
Last edited:
Upvote 0
Yes its the code that i sent above. It's so painful that i can't just post a picture and show you what i mean. Is there a way to send you a picture to show you the hidden cells?
 
Upvote 0
the code you sent above is not a worksheet event, so it should be in a normal module, not inside the worksheet code. A worksheet event is posted into the worksheet itself, and it runs based on an event like closing a workbook, changing a cell, changing a selection, etc...

The code you sent above, is not hiding any rows and therefore cannot be the issue

You can share your workbook using google drive or onedrive
 
Last edited:
Upvote 0
9rLdgL4
jWnMTrh
 
Upvote 0
it's fine i'm stupid so i just realized it was a command button. I ran the macro and it worked, then i closed the workbook and ran it again and it worked perfectly for me. I'm not sure what your issue may be, I check the worksheet events for anything that could be hiding a row and I didn't see anything. I'll keep checking but for now i'm not sure
 
Upvote 0
I do not believe this script is causing the problem you described.

I suggest you set up the sheet the way you want and run this code you showed and you will see it's not causing the issue.

There is no way you can go back now and undo what happened. Hopefully you have a Backup copy of your workbook.

Maybe you have code in a button on another sheet which refers to this sheet.

It's possible to performs scripts on a sheet even thou the code may be in another sheet or a Module script.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top