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

Young Grasshopper

Board Regular
Joined
Dec 9, 2022
Messages
58
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

VBA Code:
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:)
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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
Back
Top