Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converting formula to value and deleting rows using macros.
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. |
#2
|
|||
|
|||
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" wrote: 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. |
#3
|
|||
|
|||
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" wrote: 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" wrote: 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. |
#4
|
|||
|
|||
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" wrote: 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" wrote: 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" wrote: 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. |
#5
|
|||
|
|||
Peter,
Many thanks for your help. Sort now. Pank "Peter Rooney" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|