Adding Rows.Count to another Range

[crburke92]

Say I have a rows 10-70 to drop a CSV into, and rows 71-130 are formulas to rearrange my data into a usable way. I want to special paste just values of rows 71-131 to row 132 and on, and from there copy and paste it again to a database. If I don't fill all rows from 10-70, is there a way to use a Count to determine how many rows past 132 to go?

Sub CSVConv()
Dim ws As Worksheet
Dim TotRow As Integer
Dim CpyR As Range

TotRow = ActiveSheet.Range("A10:A70").Cells.SpecialCells(xlConstants).Count - 1
Rows("71:130").Copy
Range("A131").PasteSpecial Paste:=xlPasteValues

Rows("131" & TotRow).Copy Range("A160")

MsgBox TotRow

End Sub

MsgBox gives me a correct value of 28 for one of my CSV's (-1 for header line). is there a way to add that 28 to row 131 to essentially get Row("131:158").Copy?

Range("A160") is a temporary location, eventually it will past it to a different sheet row 2, and shift cells down..

 

[Alex Blakenburg]

I am not really sure what you are trying to do and A160 is going to overlap with your previous copy but see if this is the sort of thing you are trying to achieve.

Rows("131:" & (131 + TotRow - 1)).Copy Range("A200")

 

[crburke92]

I am not really sure what you are trying to do and A160 is going to overlap with your previous copy but see if this is the sort of thing you are trying to achieve.

Rows("131:" & (131 + TotRow - 1)).Copy Range("A200")

Sheets broken into three sections. Top section is where I drop in a CSV I get. Because the headers don’t always fall in the same place, I use a Substitute, Address and Match formula to rearrange all them into to the same column based on header name. I generally copy that by hand and paste the values in the third section so it gets rid of the formula ref. Copy that again and insert it in row 2 of a different worksheet (database). I insert it in row two because I then have a form that recalls data top to bottom based on serial number, top being most recent, bottom being furthest back. Just trying to streamline the copying so new hires can do it easily without overwriting the wrong stuff. Thanks for the answer, seems to be working now!

 

[Alex Blakenburg]

Glad I could help.
You might want to consider looking into Power Query. From what you are describing it might be quick win for you.

 

[crburke92]

Glad I could help.
You might want to consider looking into Power Query. From what you are describing it might be quick win for you.

I intend on spending my work downtime this year looking into it. Everything excel and VBA related is self taught so I’m pretty slow and probably inefficient. Does the trick though! Thanks!