Excel VBA Ranges & Rows

[S1LV3RF0X87]

Hi All,

Can someone please tweak this bit of code for me so instead of it looking at all rows in column "A" that it only looks at rows within a range of column "A".

I need the range to be "A1:A1107"

Please Login or Register  to view this content.

Thanks in advance

[MarMo]

Try adding .rows to your range :

Please Login or Register  to view this content.

[S1LV3RF0X87]

Hi Marmo,

Thanks for your reply.

I have tried your suggestion but when running the code its still taking in excess of 6 minutes to run which seems to tell me its still looking at all the rows within excel when running and not limiting the rows to the specific range.

If you have any other ideas that would be appreciated.

Its probably something really simple that i am over looking.

[MarMo]

I ran your code as i posted it in post #2, and it works just fine.

You did not post your entire code procedure, so i cannot see where the problem might be.
It's perhaps in another part of your code?

[Norie]

Have you checked what the value of LastRow is?

[S1LV3RF0X87]

Hi Norie,

When checking the value of Last Row it comes back as Cells.Rows.Count = 1048576

This is where i need to change this so it looks at the range rather than the last row inside excel.

Sorry may have misread then question.

LastRow = 0

Cells.Rows.Count = 1048576

[MarMo]

So if you have 1048576, then your code does not see the last row in column A.
Is there any data in column A?

[S1LV3RF0X87]

Yes, each row either has a 1 or a 2.

I then have the formula hide all the rows that have a 1 in column A.

My data set only goes down to "A1107" so after that the rows are blank

[MarMo]

Can you upload a sample WB as is with the data in column A, the other stuff you can delete for this purpose.
Do not copy/paste the data pls, but save the WB with another name and delete all data except column A.

It seems that for some reason your code does not intercept the last row correctly.
Eather there is no data, or it could be a cell merge from column B

[S1LV3RF0X87]

Hi Marmo

When attempting to create your example workbook for you i tested it out and the code took something like 10 seconds to run which is fantastic but raised another issue then.

If the code on a blank workbook works so quickly then what factors are taken into consideration which can slow down the code to the point on average 6 mins to completely run?

[MarMo]

I don't have a straightforward answer for this.
Can depend on different factors.
- the number of data - rows - columns to treat
- if complicated calculations are run from the code
- the performance of the computer itself, meaning CPU - memory - age of the PC , etc...

i have written code for my purpose at work, which has to deal with:
- Column deletion
- column moving
- adding columns - splitting columns
- adding formulas - copying those values
- adding Pivot table + filtering

At first the code ran at approx. 20 sec, depending on the amount of rows in the sheet.
The more rows, the more time it takes to run the code.
Now it can take up to 2 or 3 minutes, depending on the amount of data.