Young Grasshopper
Board Regular
- Joined
- Dec 9, 2022
- Messages
- 58
- Office Version
- 365
- 2016
- Platform
- 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:
Would appreciate any help
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