Range1 I6:I356, Range2 K6:K356
VBA Code that will select cells in Range2 with values
greater than zero, copy and paste in Range1.
Skipping zeroes and blanks.
Eg. I 6, I9, & I 11 should be paste in K6, K9 & K11 respectively
Without affecting the content of cells: I7, I8, I10, I12 etc.
VBA code to select cells with value greater than zero from a range
Re: VBA code to select cells with value greater than zero from a range
Welcome to the forum!
There are two concepts used here. (1) Make range of cells matching condition. (2) Copy and paste a copied range that may or may not be contiguous.
Change the source and target range Set in Main to suit.
Always test code in a backup copy. Place code in a Module.Code
Sub Main() Dim s As Range, c As Range, u As Range, t As Range Set s = Range("E7:G11") 'Source range to copy from. Set t = Range("A1") 'Target range to paste to. 'Make range with no cell values 0 nor "". For Each c In s If c.Value = 0 Or c.Value = "" Then GoTo NextC If u Is Nothing Then Set u = c Else Set u = Union(u, c) End If NextC: Next c 'Debug.Print u.Address CopyRtoT u, t End Sub Sub CopyRtoT(r As Range, t As Range) Dim c As Range, offC As Integer, offR As Long offC = t(1).Column - r(1).Column offR = t(1).Row - r(1).Row For Each c In r c.Copy c.Offset(offR, offC) Next c End Sub
I am interested in using this VBA code, however, if I am copying data from a cell which contains a formula, it is pasting the formula rather than the value. Is it possible to only paste the value?
Use the PasteSpecial xlpastevalues functionality.
What I am trying to do is create a macro which examines data from range BF11:BF26, and if contains a 0 it skips the data or if it contains anything other than a 0, it copies/pastes the data in C11:C26.
I want the rows to be held transposing, ie, if BF11 contains a 3 then C11 will have a 3. On the flip side, if BF12 contains a 0, C12 will not be changed from what is already contained.
Don’t have an account yet? Register yourself now and be a part of our community!