# Making formula calculate range only trough value, and skipping formulas.



## Young Grasshopper (Dec 19, 2022)

Hi world, and happy Monday!

I have this sheet that has 100 "sections"/Ranges. Each section includes four columns, then it skips a column, and then there is another "section".  
The first two columns in a section are filled with formulas in each cell, the last two are empty. 
I'm trying to find/make a code that copies the values of each section and pastes it underneath each other. So say C:F (in Keywords Sheet) includes three rows of data, it gets pasted in A1:D3.  Then H:K has 4 rows of data, this gets pasted in A4:D8, and so on.. 
I found one sub of codes that works almost perfectly, the only problem is that it calculates the range (or where to start pasting the next section) from rows filled, including the rows with only a formula (which can result in a "blank" cell). The result is that section 1 gets pasted in A1:D3, next could be pasted in A107:D2016(<-ish), and so on. 

Is there a way to tweak this code to calculate rows of data only on values/text, and don't count formulas? Probably something simple, but I can't figure it out. 
This is the code now: 


```
Option Explicit
Sub zzzmoveData()
Dim rSource As Range, rDest As Range, r As Range
Dim tbl As Range, rowNum As Integer
Const colNum = 4

Set rDest = Range("A2")
Set rSource = ThisWorkbook.Worksheets("Keywords").Range("C5")
Set r = rSource
While r <> ""
    Set r = Range(r, r.End(xlDown))
    Set tbl = Range(r, r.Offset(0, colNum - 1))
    Set tbl = Range(tbl, tbl.End(xlDown).Offset(, 0))
    tbl.Copy
    rDest.PasteSpecial (xlPasteValues)
    Set rDest = rDest.Offset(tbl.Rows.Count, 0)
    Set r = r(1, 2)
    Set r = r.Offset(0, colNum)
Wend
End Sub
```


Would appreciate any help


----------



## Young Grasshopper (Dec 22, 2022)

Bump


----------



## Young Grasshopper (Jan 2, 2023)

Bump


----------



## Young Grasshopper (Jan 2, 2023)

solved it


----------



## Fluff (Jan 2, 2023)

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.


----------

