VBA selection choosing thousands of extra cells

**chrisellis250** · 05-09-2019, 12:13 PM

Hello,

I have a spreadsheet with a userform to manipulate data.

The userform copies in data from Sheet2 into Column J of Sheet3. In Column K I have =J2(Right,10) formula copied down to around row 200,000 - as there are occasions where I may be working with this much data.

A macro copies the data from Column K into Column L, where it is pasted as Values. This is then copied into Column A of Sheet1, where a Macro creates a PivotTable from the data.

However, the main issue I have is that the pivot created looks at a number of blanks, as it will create data from Column A in Sheet1 from A1 down to A200000.

I have tried running code to delete blanks from COlumn A before creating the PivotTable, but it still goes up to A200000.

Any ideas how to get around this?

Kind Regards,

**jolivanes** · 05-09-2019, 03:08 PM

If you save the file, inside your code, after deleting the not needed rows, does that help?

**BILLONEILL40** · 05-09-2019, 03:40 PM

I had a similar issue with the used area and getting a formula pasted to the used column next to it...

The following goes to the top cell below my title in row O and writes the formula (in your case =J2(Right,10)), it then copies it based on data in column N, pasting this formula until the end (plus one row) then I have it delete the last item in the list that was extra.

Please Login or Register  to view this content.

ct
Selection.Delete

Not sure this fixes your full issue, but it will fix the need for including 200k line items.

**chrisellis250** · 05-10-2019, 04:48 AM

What would I put for my formula on the 3rd line? Not sure how it would go in the code.

**alansidman** · 05-10-2019, 05:00 AM

@BILLONEILL40
Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

VBA selection choosing thousands of extra cells

LinkBack

Thread Tools

Rate This Thread

Display

VBA selection choosing thousands of extra cells

Re: VBA selection choosing thousands of extra cells

Re: VBA selection choosing thousands of extra cells

Re: VBA selection choosing thousands of extra cells

Re: VBA selection choosing thousands of extra cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Concatenate thousands of cells with a comma between each one?

[SOLVED] Delete Thousands of Extra Columns

populate cell with specific selection after choosing selection in dropdown in another cell

[SOLVED] Extra Extra! I need to remove extra columns and rows in my spreadsheet.

copy and pasting thousands of cells.

Adding together thousands of sets without thousands of formulas!

need to add/remove characters over thousands of cells

Bookmarks

Bookmarks

Posting Permissions