Can this be done?

[AlfD]

Dave:

Good.

Final two checks. If these don't show up the problem, send me a copy o
the workbook. (Probably should have done this a while ago, but isn'
hindsight wonderful?)

First: hover your cursor over the variable r anywhere it occurs in th
script. Does it report 100? (You said there were 100 lines.) Did yo
change the 1000 to 100 in the For r=100 to 2 step -1?

Second: does your code match the following precisely?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = True
Dim r As Integer
Dim s As Variant

For r = 100 To 2 Step -1
s = Range("#" & r).Value
If IsNumeric(s) Then
If s > 3 Then Rows(r - 1).Delete
End If
Next r
End Sub

where # represents the letter of the column with the numbers generate
by Subtotal.

If all is well in these terms and it still doesn't work, send it an
I'll see what I can find under the bonnet.

Al

 

[Dark Horse]

Alf,
It read r=100.

I have tried the code with both A and B as I wasn't sure which one I should
be using. Should I use the title/heading of the column?
I hadn't changed the 1000 to 100, but I have now and I still get no results.
I started with 100 lines, but that becomes 158 by the time the subtotals are
added = and yes, I did try that too.

Should I send to you now?

 

[AlfD]

Dave:

Yes, certainly.

Can you find the address?

Al

 

[Dark Horse]

I'll have a think about it, and another crack at debugging first - there
must be something simple.

 

[AlfD]

Hi Dave:

OK.

You were right about the 158: lines is the issue, not records.

On which point, if 100 records generate 158 lines, just how man
multiples have you got in your sample? Crucially, how many over
occurrences?

Al

 

[Dark Horse]

My sample/test is just the first 100 lines from a sheet.
and there are just two examples over 3 occurrences.

I have got something to work at last, unfortunately it deletes everything
except the first three lines! That isn't one that is over limit either.

s= Range ("#" & r). Value

What should # be?

My original sheet was names in A; but since adding subtotals I now have Name
Count in A and a number in B underneath the list of names.
A B
No title Name
_______ Alan
_______ Alan
_______ Alan
_______ Alan
Alan Count 4

Should # become A or B, or something else?
Because if it is A or B they both wipe everything except the top
3 lines. In that case - IF either A or B is correct, there must be an error
in the
delete command?

Dave

 

[AlfD]

Hi Dave:

I said 158 was OK. On second thoughts, is 158 the line at the botto
with a Grand Total at the end of your count?

If it is, you'll need 157. Otherwise the sub will go on stripping ou
rows until the 158 reduces to 3 and leaves you 3 records at the top
Sound familiar?

Al

 

[Dark Horse]

Hi Alf,
158 was/is the line at the bottom with Grand Count at the bottom and 99 in
column B.
So, I tried 157 and the same thing happened again.
So, I thought - nothing ventured nothing gained - and tried 99 instead of
what had gone before.
That had the same end result, so I tried 98 next.
Once again the result was exactly the same, just the top 3 lines left with
Name Count and Grand Count beneath those - which both have an accompanying
number of 3 in column B.

So I have now tried 158 with # = A, 158 with # = B, 157 with # = A, 157 with
# = B, 99 with # = A, 99 with # = B, and just for good measure 159 with # =
A, 159 with # = B, 100 with # = A, and 100 with # = B.

Something niggled though, and there weren't enough examples with more than 3
entries so I scrapped everything and started again.
My new testbed was 200 rows, giving a GrandCount of 199 - and I put all
subtotals in Column D too, just to try something different.
200 rows gave me GrandCount in row 315 with a total of 199 in column D.
Restart the merry-go-round!

But first, one question you have yet to answer.
s = range("#" & r). Value
What DOES go in place of #? Is it - in my new version - D?
Or something else?
That will at least cut down on my testing time by a third.

Cheers
Dave

 

[AlfD]

Dave:

These are not random, guessable criteria and events: they ar
reasonable - i.e. susceptible to reason.

My column H refers to the column in which the Subtotals routine put
numbers (3,4,5 etc). The reason is because the test :"is it greate
than 3?" refers to the numbers in that column (and, in between them, t
whatever else is in that column. But those items don't matter because
long way back we agreed that a column would be chosen which didn't hav
numbers in it greater than 3.) Sounds like your latest choice is D. OK
so long as it doesn't contravene the above conditions. Please tell m
what D contains.

The first number of the if...then loop is precisely the **row number*
of the row above the Grand Count. Not the Grand Count value which wil
be different because it doesn't count the subtotal lines. Sounds lik
it's 314 in your 200 sample.

I don't understand the reference to 98 in Col B.

If this works: fine. If not, send me that snapshot.


Al

 

[Dark Horse]

Alf,
98 was one less than 99, and just one more thing to try before introducing
brick wall to head!

I am now trying 314 and D in place of #.
s=Range("D" & r).value
Column D has only letters in it until I use it for subtotal.

One thing concerns me slightly.
Most of, if not all of, the time I can recall - whenever an action is taking
place, I am looking at an hourglass.
That is not happening here when I press the command button, so could
something be wrong there?

From that, when I wrote the above I suddenly wondered if I had put "" around
D - so pressed alt+F11 to check, and the code sheet was empty!
Pasted code in again, and pressed button - and it worked!!!!
I even got the hourglass.
Is there some way of saving the code that I am not doing right?

Now.....
From here, how do I remove the command button, the counts, and the numbers
in column D so that I can then run my next macro.
Next, can I save this process as a macro rather than use a command button?
We are getting there.
Dave

 

[AlfD]

Dave:

Good news indeed.

Next to run it on a bigger sample - or a full-scale model. I'll leav
you to try that. Principles are unchanged: only the row number will b
different. (And the purists would tell me to let Excel count the numbe
of rows. Maybe: but not until the rest is secure).

To get rid of the subtotals go to Data>Subtotals...>Remove all (ignor
the other boxes etc). BTW, if you weren't taken straight to th
SubTotals dialogue box, you probably hadn't clicked on a cel
beforehand (to tell Excel which table it was dealing with).

I'll come to the macro later.

There's still the issue of longer-term maintenance to deal with. To us
that lovely acronym, AFAIK there are no worksheet functions whic
physically take out rows. For sure, I only know how to do it via th
keyboard and via VBA. Maintenance through KB is what we have bee
getting rid of for the last n posts. So you need a macro or sub to d
the other one too. Well, you've just about got one. When appropriate
you could run the sub you've now got and get rid of the silt. Is i
daily, weekly, monthly? A bit of arithmetic will tell you how much yo
silt up in a given period.

Yes: you can get rid of the button on the screen and - simplest way
have a keyboard shortcut. I fancy Ctrl-Shift-r ( for control and shif
rubbish ). But the choice will have to be yours: you may already hav
shortcuts which you use and don't want to disturb.

Have you ever recorded a macro?

Al

 

[Dark Horse]

I tried it on a larger sample, and again it didn't work and I had to
reinstall the code again - and then it worked.
There seems to be something I am not doing correctly when inserting the
code, and that must have been the problem all along.
Trouble is I don't know what it is I am doing wrong.

I open the page by alt + F11 and insert the code, close that down and then
create button - but that doesn't seem to let the two components (code and
button) recognise each other.

I haven't ever recorded a macro but I have inserted one and debugged it to
get it running.

I can get rid of subtotals fine.

This will be a daily job, so I may as well leave the button - I can always
move it, unless this does turn into a macro. That would be nice, and much
simpler.
Cheers

Half way there?
Dave

 

[AlfD]

Dave:

Why push our luck? OK we'll stick with a button (make it small, make i
pretty, give it a caption : you can do all these things in th
Properties window).

Still the issue of connecting the button and the sub. My creating th
sub followed by the button was a bit cart-before-horse but I don't se
that being a cause of strife. So: let's record a useless macro to se
if the problem of registering our macro (sub) can be cracked.

Can you record a simple macro as follows:

Tools>Macro>Record New Macro. This opens a dialogue box. Let th
defaults stand and don't bother with a shortcut key. But where it ask
you where to store it, select Personal Macro Workbook. Click OK.

Now you are recording your every move (well, almost), so do something
select a cell: make its contents bold. That's enough.

Back to Tools>Macro>Stop recording.

Redo your weeding sub routine.

This should ensure next time you open it, it will be there to use. I
it doesn't, I shall shout for help.

You said "Halfway there?". If it's less than 90% I'll be {surprised
shocked, disappointed, worried, damned}. Select one or more string
from the array.

Al

 

[Dark Horse]

Hi Alf,
I can do that, but I will have to edit it every time before I run it won't
I?
Because the number of rows will be unique and different every time.
I have used the record, stop, edit, paste way to move/copy a macro between
workbooks before.
Cheers
Dave

"AlfD >" <<AlfD.13zzd7@excelforum-
nospam.com> wrote in message news:[email protected]...

 

[AlfD]

Dave:
As I said some time ago (amazingly, I think it was earlier today)

"(And the purists would tell me to let Excel count the number of rows
Maybe: but not until the rest is secure)"

It's one of the most common requests on the forum, so far as I can see
But I judged we needed the greater transparency of seeing what wa
going on. My judgement right or wrong!

Meanwhile: have you cemented the code into your system or is it stil
being volatile/fugitive? (Not a technical term). Get that fixed, the
we can look at the odd line of code needed to let Excel count th
rows.

Al

 

[Dark Horse]

Hi Alf,
I think the best way to get the code in is to create the button and enter
the code via the properties box for that.
That is the only way that has never failed, maybe it provides a unique
connection, I really don't know - but I do know it works and any other way
is hit or miss.
I tried it tonight in a copy of my full scale biggest sheet, and it all
worked fine - apart from taking too much memory to allow undo and 63 minutes
of waiting and wondering.
Dave

 

[AlfD]

Hi Dave:

Good. If it works, use it.

Problem of size has always worried me ever since you characterised you
kit as antediluvian. Maybe the issue of tools and jobs is a topic fo
further thought. But that (literally, I fear) is your problem.

However: it worked.

You can split your data down into more manageable chunks and proces
it. Then re-integrate it. The sorting process at the beginning shoul
ensure you won't miss any pruning by doing that.

The issue of getting the right number of rows might now properly b
addressed. Use the following replacement for your erstwhile sub. All i
does is count up from the bottom of the spreadsheet until it hits a ro
with data. We know, of course, that that is the row with the Gran
Count in it. (Sounds like a latter day European luminary...). So w
take one off to determine the size of the thing.
If the size changes, Size changes

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False ' Saves time
Dim r As Integer
Dim s As Variant
Dim Size As Long

Size = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows
xlPrevious).Row - 1
For r = Size To 2 Step -1
s = Range("D" & r).Value 'We did decide it was D, I recall

If IsNumeric(s) Then
If s > 3 Then Rows(r - 1).Delete
End If

Next r
End Sub


Maybe you'll give me a profile of you PC sometime: CPU, memory, H
size. I'll be interested to see it.

Try this on different sized samples ( small ones to save time).
For what it's worth (is there an acronym FWIW?) it takes about
minutes on my machine to process my 14500+ records. I wish I coul
think of some way to speed up the process, but nothing springs t
mind.

Al

 

[Dark Horse]

Hi Alf,

Size = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows,
xlPrevious).Row - 1

I get a compile error: syntax error on that line.
Should I have replaced the * with something, and if so, what?

Dave

 

[AlfD]

Hi Dave:

No: don't replace the asterisk *. Its job is as a wildcard - to b
"matched" if anything at all is encountered in a cell when moving u
the worksheet.

The code works for me: it doesn't work for you. Your posted copy of i
matches mine with one small difference - see below.

A syntax error would indicate a problem with the words on the page. I
the faulty line typed in entirely on one line or has a break crept i
(as it has in your posted message)? Funny, though: that sort of proble
should show up at editing time (red letters: rude messages). Any o
that?

BTW, which versions of Windows and Excel are you using? Something als
ark-aic? Not that I'm saying this is relevant: just interested.

Al

 

[Dark Horse]

Hi Alf,
I'm unsure how it crept in, I just copied and pasted from your post.
However, xlFormulas, , xlByRows,
should that gap be between the two comma's?
Should there be two comma's?
Is 42 really the meaning of life, or is it Chelsea 4 Arsenal 0?
Cheers
Dave