Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2020, 02:18 AM
kippiebla kippiebla is offline Delete rows when copying table Windows XP Delete rows when copying table Office 2016
Novice
Delete rows when copying table
 
Join Date: Aug 2016
Posts: 4
kippiebla is on a distinguished road
Default Delete rows when copying table

I am not a star in Excel and certainly a newbie in VBA. In the attached file you see in worksheet "Source" in column C different reviews. Among these reviews are also "Error" and "Not good". The "Copy" worksheet should be a copy of the "Source" worksheet, but the rows with "Error" and "False" should not be copied. These rows may be left empty, but it would even be better of it is possible to leave them out entirely so that only 4 rows remain in worksheet "Copy".

I have been sent a code that should do it. That code is:

Sub Ja()
With Sheets("bron").Range("A4:C11") 'je bron
.Parent.AutoFilterMode = False 'filter uitzetten in blad
.AutoFilter Field:=3, Criteria1:=Array("Ja", "Ja goed", "Ja ok", "nog een andere reden"), Operator:=xlFilterValues 'array met alles dat wel meemag
.Copy Sheets("Kopie").Range("E4") 'doorkopieren
.Parent.AutoFilterMode = False 'filter uitzetten
End With


End Sub

But now I don't know what to do with that code at all. Where to put it. Can anyone help?
Attached Files
File Type: xlsx Example Copy Sheets.xlsx (12.0 KB, 6 views)
Reply With Quote
  #2  
Old 05-09-2020, 11:36 AM
BobBridges's Avatar
BobBridges BobBridges is offline Delete rows when copying table Windows 7 64bit Delete rows when copying table Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

You need to paste the code into a VBA module. How you do that may vary a bit with the version of Excel you're using, but try this:

1) Enable the "Developer" tab. This is hidden by default, but if you're going to start doing anything in VBA you'll want it visible. If you don't see it up at the top of your Excel window along with File, Home, Insert, etc, turn it on this way:

1a) Go to File, Options.
1b) Select Customize Ribbon in the left panel.
1c) On the right side of the dialogue, check the box labeled "Developer".
1d) Click on OK and return to normal Excel operation. You should now see the Developer option in your ribbon.

2) Click on Developer, then select Visual Basic (at the far left).

This opens the VBA Editor. You may need some time and experimentation to find your way around here, but in the Project (sub-)window you'll see a list of your open workbooks, and the worksheets in each.

3) Right-click in the project window anywhere on the workbook where you want to save the code.

4) Select "Insert", then "Module".

A new module appears. Paste your code in there.

Starting with Excel 2007 (I think), when you save the workbook after that, Excel will complain that you'll lose that code if you save it as .xlsx; you'll have to save it as .xlsm to keep the VBA code. And when it comes to running that program, maybe you'll need help with that too. But enough to start with...I hope :-).

Reply With Quote
  #3  
Old 05-09-2020, 02:40 PM
macropod's Avatar
macropod macropod is offline Delete rows when copying table Windows 7 64bit Delete rows when copying table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: Delete rows when copying table
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 05-09-2020, 03:17 PM
kippiebla kippiebla is offline Delete rows when copying table Windows XP Delete rows when copying table Office 2016
Novice
Delete rows when copying table
 
Join Date: Aug 2016
Posts: 4
kippiebla is on a distinguished road
Default

Thanks Bob! I will study everything carefully. Thanks a lot!
Reply With Quote
  #5  
Old 05-10-2020, 12:18 AM
kippiebla kippiebla is offline Delete rows when copying table Windows XP Delete rows when copying table Office 2016
Novice
Delete rows when copying table
 
Join Date: Aug 2016
Posts: 4
kippiebla is on a distinguished road
Default

Thank Bob, It works!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete rows when copying table Macro delete table rows if cell in first column = $ Btop Word VBA 7 02-13-2018 05:36 PM
Delete rows when copying table Delete Empty Table Rows cltay87 Word VBA 4 02-27-2017 04:23 AM
Copying specific columns of a table to WORD and deleting rows ffinley Word VBA 5 12-07-2015 04:01 PM
Delete rows when copying table How to delete the two non-adjacent rows in a table Word beginner Word 2 01-05-2015 05:47 AM
Delete rows when copying table Delete Rows in Protected Table with Form Fields Elan05 Word VBA 23 09-11-2014 12:47 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft