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.

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

Kenneth,

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.