I am trying to create a macro that will scan through a particular column within the Raw data Import worksheet which may be really long (up to 7,000 rows) and delete any rows that do not have the sample name of Initial or does not contain "week" in the name and bring this data over to the reformat page. I have created a formula within the sample name in the reformat page that looks for the word "Initial" and adds a "0-" in front of it. If that can be added in the macro it would be great!!!! I only need to bring over the columns indicated in the reformat page.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Sorry I thought I explained everything. Within the Raw Data Import sheet you are looking at the Sample Name column. There, any value that doesn't have to word "Initial" or contains the word "week" in the name is to be deleted. Once you have that final list, I need the Sample Name, Vial, Vial ID, Retention & Area columns to go over into the Reformat Data sheet. Before the Sample Name gets placed there I would need to look through the Sample name again and any row with the Sample Name of "Initial" I want to add a prefix of "0-" to the front of it. Hopefully this explains better what I need. Thank you so much for your help and please let me know if you need any further information.
Sub Demo1() Const C = 14, D = "Reformat Data" Dim Rg As Range, L& With Worksheets("Raw Data Import").UsedRange.Resize(, C) L = .Rows.Count If L = 1 Then Beep: Exit Sub Application.ScreenUpdating = False .Cells(2, C).Resize(L - 1).Formula = "=(B2<>""INITIAL"")+ISERR(SEARCH("" week "",B2))=2" .Sort .Cells(C), xlAscending, Header:=xlYes Set Rg = .Columns(C).Find(True, , xlValues) If Not Rg Is Nothing Then .Rows(Rg.Row & ":" & L).Clear Set Rg = Nothing End If .Columns(C).Clear .AdvancedFilter xlFilterCopy, , Worksheets(D).UsedRange.Rows(1) End With Worksheets(D).UsedRange.Columns(1).Replace "INITIAL", "0-INITIAL", xlWhole Application.ScreenUpdating = True End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 04-06-2017 at 08:16 PM.
Reason: optimization …
Hi and thank you sooo much!!!
It works great. Is it possible for you to send me an explanation of what it all means? I was going through the code trying to figure out how it works and can't even get how did you pick the right columns to copy over.
Const C=14 is this the entire range to be worked on?
L& what is that? I think it's the range of rows to be worked on but why did you need to add& to it?
So you name the entire range with the next statement, then say if there's only one row then beep and exit. Why Beep?
Set the screen to not flicker while processing.
After that I'm not sure what is happening but think this is where you are deleting the rows that don't meet the criteria.
Then you do the copy/paste of the data but not sure how you picked the correct columns.
After that you change the naming of Initial and update the screen.
If you can fill in the blanks for me to better understand I would greatly appreciate it. I'm trying to learn so if I ever need to adjust this in some way I know what I need to do and go to make the changes.
When you like a code, show it by clicking on post bottom left star Add Reputation, thanks !
C constant is just the column number where formula stands for marking rows to delete …
L variable is for worksheet last row used,
the sign & is a character to declare its type as you can read in VBA inner help of Long type.
If last row is row #1 so there is nothing to do what's the beep warns
and this test avoids an execution error under with the Resize statement …
As deleting row by row is slower than clearing a single rows block the reason of using a sort.
As Excel inner features are often faster than any code looping row by row !
There is not a Copy/Paste but again an Excel basics feature :
an advanced filter which picks up data according to result worksheet columns headers.
Notice you can get same result worksheet without deleting anything in source worksheet
just directly using an advanced filter with half of codelines !
LinkBack URL
About LinkBacks
Register To Reply
Hi ! Try this demonstration !
Re: Deleting Unwanted rows
Bookmarks