Select to end of table for all values <1000

[ChrisJ]

I've got a table from File Server Resources Manager reports that lists
Owner, Total Size on Disk and Files in three columns.

I want to extract from the report just the rows from the table down to
where Total Size on Disk is < 1000 so that:

Owner Total Size Files
user1 21,054 MB 206
user2 1,052 MB 832
user3 1,008 MB 1423
user4 999 MB 1075
user5 992 MB 504
user6 983 MB 956
user7 981 MB 547
user8 973 MB 405
user9 972 MB 314
user10 970 MB 1412
user11 967 MB 1566
user12 961 MB 1920
User13 958 MB 554
User14 57.0 MB 49
User15 1.24 MB 1


becomes

Owner Total Size Files
user1 21,054 MB 206
user2 1,052 MB 832
user3 1,008 MB 1423


I tried

with Selection.find
..Text = "<9?? MB"
..MatchWildcards=true
end with
Selection.find.execute

But the numbers over 999 have a "," in them which < thinks means is a
word separator so it matches all numbers like 1,999 MB, 32,999 MB etc
Also 9?? assumes that there will always be a user with 9hundred and
something MB.

I tried

with Selection.find
..Text = "[0-9]{1,3} MB"
..MatchWildcards=true
end with
Selection.find.execute

but this only matches 999, 888, 777 etc and misses 55.5 and 1.20etc
because of the decimal point

Once I've found the line thats <1000 MB I then need to select the row,
expand the selection to the end of the table and delete the rows so
that I can then format the remaining table rows and fonts and copy the
rest of the table to the clipboard ready to insert in the new report.

Any ideas?

 

[Harold Druss]

I've got a table from File Server Resources Manager reports that lists
Owner, Total Size on Disk and Files in three columns.

I want to extract from the report just the rows from the table down to
where Total Size on Disk is < 1000 so that:

Owner Total Size Files
user1 21,054 MB 206
user2 1,052 MB 832
user3 1,008 MB 1423
user4 999 MB 1075
user5 992 MB 504
user6 983 MB 956
user7 981 MB 547
user8 973 MB 405
user9 972 MB 314
user10 970 MB 1412
user11 967 MB 1566
user12 961 MB 1920
User13 958 MB 554
User14 57.0 MB 49
User15 1.24 MB 1


becomes

Owner Total Size Files
user1 21,054 MB 206
user2 1,052 MB 832
user3 1,008 MB 1423


I tried

with Selection.find
.Text = "<9?? MB"
.MatchWildcards=true
end with
Selection.find.execute

But the numbers over 999 have a "," in them which < thinks means is a
word separator so it matches all numbers like 1,999 MB, 32,999 MB etc
Also 9?? assumes that there will always be a user with 9hundred and
something MB.

I tried

with Selection.find
.Text = "[0-9]{1,3} MB"
.MatchWildcards=true
end with
Selection.find.execute

but this only matches 999, 888, 777 etc and misses 55.5 and 1.20etc
because of the decimal point

Once I've found the line thats <1000 MB I then need to select the row,
expand the selection to the end of the table and delete the rows so
that I can then format the remaining table rows and fonts and copy the
rest of the table to the clipboard ready to insert in the new report.

Any ideas?

Hi Chris
This macro should do what you want
===============================================
Sub test()
Dim i As Long
Dim a As Variant
Dim b As String
For i = ActiveDocument.Tables(1).Rows.Count To 2 Step -1
a = Split(ActiveDocument.Tables(1).Cell(i, 2).Range.Text, " ")
b = Replace(a(0), ",", "")
If Val(b) < 1000 Then ActiveDocument.Tables(1).Rows(i).Delete
Next
End Sub
==============================================
Good luck
Harold