Converting formula to value and deleting rows using macros.

[Pank]

I have a header, variable number of data lines and a trailer.

On the trailer in cell Bn I have the formula =COUNTROWS(B:B) – 2. I need to
delete all the data lines and therefore I need a mechanism to convert the
formula to a physical value.

I have tried the following code:-

With wks
lLastRow = Range("B1").End(xlDown).Row
Selection.NumberFormat = "General"
Selection.Copy
.Range(lLastRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End With

But unfortunately I get the message ‘run-time error 44’. Debug points to
..Range(lLastRow).Select

Additionally I need to delete all data records (i.e. from Row 2 to lLastRow
= Range("B1").End(xlDown) – 1). How do I code that?

Any help appreciated.

 

[Peter Rooney]

Pank,

Have you tried using currentregion?

The way I'd go about this would be to put my trailer formulae in my header,
then all you need to worry about is working out how many data rows you need
to delete.
Make sure that your database follows a few simple rules:
Don't have any blank rows in it
Make sure that there's no blank row between your field headers and your
first row.
Make sure that there's a blank row between the field headers and anything in
your header rows.

Then, assuming that your first field header has a name of "DatabaseHeader",
try:

Dim DBSheet As Worksheet
Dim DBStart As Range
Dim DBCR As Range
Set DBSheet = Worksheets("Database")
Set DBStart = DBSheet.Range("DatabaseStart")
Set DBCR = DBStart.CurrentRegion

The currentregion will include the field headers and the data, so you have
to offset down by 1 row to select just the data rows. The syntax here is:

DBStart.offset(RowsToOffset, ColumnsToOffset).RESIZE(height of range in
rows, width of range in columns)

DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name =
"Data"

You now have a named range called "Data" which you can delete using
Range("Data").clear

In terms of pasting your header formula to values, try:

Range("Data").select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
application.cutcopymode=false (to empty the Clipboard)

Hope this helps

Pete

 

[Pank]

Pete,

Firstly, many thanks for you help.

However, when I run the macro it returns a Run-Time error 1004. When I
select debug, it point to Set DBStart = DBSheet.Range("DatabaseStart")

Any ideas as to what is causing the error?


The macro reads:-

Sub Macro1()

Dim DBSheet As Worksheet
Dim DBStart As Range
Dim DBCR As Range
Set DBSheet = Worksheets("Main")
Set DBStart = DBSheet.Range("DatabaseStart")
Set DBCR = DBStart.CurrentRegion

'The currentregion will include the field headers and the data, so you have
'to offset down by 1 row to select just the data rows. The syntax here is:

'.DBStart.offset(RowsToOffset, ColumnsToOffset).RESIZE(height of range in
'rows, width of range in columns)

DBStart.Offset(1, 0).Resize(DBCR.Rows.Count - 1, DBCR.Columns.Count).Name =
"Data"
Range("Data").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'application.cutcopymode=false (to empty the Clipboard)


Regards

Pank

 

[Peter Rooney]

Oops - the field header of the first field in your database needs to have the
range name "DatabaseStart" for this to work. I do all my databases like this,
and I forgot to mention it - the great danger of assuming that everyone knows
what youre talking about just because you do!

Pete



Sorry

 

[Pank]

Peter,

Many thanks for your help. Sort now.

Pank