Macro to value Data

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
I have data with Formula's in the following columns and rows



B18:M18 (Text in A18 Dept1)
B31:M31 (Text in A31 Dept2)
B45:M45 (Text in A31 Dept3)
B61:M61 (Text in A31 Dept4)

I would like to know if there is a better way to write the code, possible using range names as the rows may change from time to time so the ranges would have to be amended in the code


Your assistance in this regard is most appreciated


Code:
Sub Range_ValPRofits_BR1()
With Sheets("BR1")
.Range("B18:M18").Copy
.Range("B19").PasteSpecial Paste:=xlPasteValues
.Range("B31:M31").Copy
.Range("B32").PasteSpecial Paste:=xlPasteValues
.Range("B45:M45").Copy
.Range("B46").PasteSpecial Paste:=xlPasteValues
.Range("B61:M61").Copy
.Range("B62").PasteSpecial Paste:=xlPasteValues
   .Application.CutCopyMode = False
   End With
  
End Sub
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You could consider placing the affected addresses in an array. If you want to avoid future necessary changes to your code, the use of named ranges is recommended.

VBA Code:
Sub Range_ValPRofits_BR1()
    Dim arr As Variant
    Dim n   As Variant

    arr = Array("B18:M18", "B31:M31", "B45:M45", "B61:M61")

    With Sheets("BR1")
        For Each n In arr
            .Range(n).Copy
            .Range(n).Offset(1, 0).PasteSpecial xlPasteValues
        Next n
    End With
    Application.CutCopyMode = False
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the Help. Instead in using B18:M18 in the array, could I named the range say BR1_Dep1 and the use BR1_Dep1 in the array ?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Just let you know that I have sorted out the range names in the array
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Your are welcome and thanks for letting me know. Glad it's sorted (y)
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Hi GWteB

I have used the following code with you kindly helped me with to range value certain rows and columns using range values


Where row 5 eg B5 contains "Current", then the row in that column must not be range valued i.e copied and paste valued


It would be appreciated if you could kindly amend my formula to accommodate this

Code:
 Sub Range_ValPRofits_BR1()

 Dim arr As Variant
    Dim n   As Variant

    arr = Array("NVNP_BR1", "UVNP_BR1", "SVCNP_BR1", "totalNP_Br1")

   With Sheets("BR1")
For Each n In arr
.Range(n).Copy
.Range(n).Offset(1, 0).PasteSpecial xlPasteValues
Next n

End With
Application.CutCopyMode = False
Range("a1").Select

   
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
If I understood correctly, you are looking for something like this ...

Rich (BB code):
Sub Range_ValPRofits_BR1()

    Dim arr As Variant
    Dim n   As Variant
    
    arr = Array("NVNP_BR1", "UVNP_BR1", "SVCNP_BR1", "totalNP_Br1")
    
    With Sheets("BR1")
        For Each n In arr
            If Not InStr(1, .Range("B5").Value, "current", vbTextCompare) > 0 Then
                .Range(n).Copy
                .Range(n).Offset(1, 0).PasteSpecial xlPasteValues
            End If
        Next n
    End With
    Application.CutCopyMode = False
    
    Range("a1").Select
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,751
Office Version
  1. 2019
Platform
  1. Windows
Thanks or the help. Where row 5 contains "Current" which in the example is B5 , then only Col B i.e B9 must not be copied to B10. C9:M9 must still be copied and pasted to C10 onwards (C10:M10) using pastespecial Values

If B5, C5 contained "Current" then these colulunns must be ignored and D9:M9 to be copies and pasted as values into D10 onwards


Kindly amend your code accordingly
 

Watch MrExcel Video

Forum statistics

Threads
1,127,765
Messages
5,626,738
Members
416,201
Latest member
brianhf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top