zfeld75
New Member
- Joined
- May 30, 2017
- Messages
- 7
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
I wrote a macro (mostly by recording it) that copies data from a section on one sheet then calculates the end of my table on another sheet and pastes (paste special, being that the data I am pasting is a formula and I need to paste the values) the data to the end of my table, which on its own increases the size of my table.
That works.
My problem is that I am not sure how much of my original range of data (that I am copying) will actually have values in it (there is a formula that is either giving it a value or ""), so I take a large range, just in case
So.... after I pasted it I would like to go through my table and remove any rows that were added that only had empty strings ("") and no values, and then resize the table so it is only as large as the rows that have data.
These rows can be in the middle or at the end of my pasted data.
I need help on the VBA code to do that.
I may also need to clear the formatting that the table automatically added to those additional rows
here is the code I have until now
That works.
My problem is that I am not sure how much of my original range of data (that I am copying) will actually have values in it (there is a formula that is either giving it a value or ""), so I take a large range, just in case
So.... after I pasted it I would like to go through my table and remove any rows that were added that only had empty strings ("") and no values, and then resize the table so it is only as large as the rows that have data.
These rows can be in the middle or at the end of my pasted data.
I need help on the VBA code to do that.
I may also need to clear the formatting that the table automatically added to those additional rows
here is the code I have until now
Code:
Range("O7:R30").Select
Selection.Copy
Sheets("deposits").Select
Dim lastRow As Long
lastRow = ActiveSheet.ListObjects("deposits").Range.Rows.Count
Range("A" & lastRow).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False