Sorting and deleting rows

I have a code that auto-fills down for 200 rows, then deletes the unused rows and sorts them based on data in some of the cells, however when it sorts it does the entire worksheet and not row 6 and onwards as I had intended, also for selecting the rows to delete I had to copy and paste the same code over and over i was wondering if there is a simpler way to do this. Thank you for any assistance you can give me.

Please Login or Register  to view this content.

Hello,

Is it possible for you to include a copy of your workbook?

this is a sample file ive been working with it should have all the information involved. basically select the first sheet and then run the macro.
example.xlsm

Is the "CT1+CB-A1" will ever change?

This is whats you are doing.
1. You have a sheet "Formula" with some kind of sheet template that then you copy to a new sheet.
2. You replace the constants with some data in the formulas to make reference to the sheets.
3. you fill down your columns to the range.

Couldn't just write the formulas in the code and then just put them the way they should be there?

Thanks for the input

OK give this try and let me know if it works. I modified your code a little bit as well as your workbook to what I think is a better solution.

In there workbook you will now find a hidden sheet with a dynamic range to cut down the several ORs you had in one of your columns. I also hid the Formula sheet because its not necessary for our task. We will leave it hidden there as reference as well


Regarding your code:

1. If want you need is data then you copy from a source but if you need a template just write it on the code.

2. Autofill is a little tricky and can slow down big workbooks, it also make your screen flicker. Instead of that use an R1C1 formula and paste in every single cell you need them. My code might seem more complex than then one you had but in the end the outcome will be more efficient. Think of it as two steps (yours) :
-Put a formula in A1
-Autofill until A10
One step (mine)
-Put a relative formula in cells A1 to A10

Note: Making an R1C1 formula is very easy just turn on the macro recorder, select the cell with the formula, hit enter, stop recording...and voila

3. If you need to perform th same task several times or if your code starts getting too long think in modularization. In other words the breaking down of your code into smaller pieces that you will later assemble together. Look at my code to see what I mean. BTW in my code even though ther are more than one macro the only one you have to execute is WireDataMacro.

I hope all this helps you out in your coding.

Thanks

Here is the File Example.xlsm

And here is the code:

Please Login or Register  to view this content.

Thanks

Does the sample I gave you work?

not exactly i need the old worksheet name to stay variable so that i can use it from other sheets, im going to try and impliment it into the part of your formula that has the equations so that i dont need the formulas sheet next.

Ohok sorry about that I will fix it. This afternoon I am at work now. Basically all you have to do is change the name of the sheet and use the reference sheet variable from the beginning of the code? I believe it's strOS

So if the code says 'sheet1'! Change it to '" & strOS & "'!

I wish i could help you more right now p, but I am at work now.

Try typing spaces in between

Hi Lotrking1010 ,

I am sorry for the delay. The issue is that we were using variable "strOS" that was defined in a different subroutine. So created a new variable in the new sheet to use it as the sheet name. I am sorry about that.

So here is the code for the main Routine

Please Login or Register  to view this content.

Here you have the formula filler

Please Login or Register  to view this content.

And last but nor least the Filter. Which didn't change at all

Please Login or Register  to view this content.

If you have any questions please let me know.

Example.xlsm