+ Reply to Thread
Results 1 to 11 of 11

Excel VBA Ranges & Rows

  1. #1
    Registered User
    Join Date
    07-13-2018
    Location
    Liverpool
    MS-Off Ver
    Office 365
    Posts
    8

    Post Excel VBA Ranges & Rows

    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

  2. #2
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: Excel VBA Ranges & Rows

    Try adding .rows to your range :


    Please Login or Register  to view this content.
    Even a little help can be a big help !!
    1. A sample workbook says more then words. Add problem description and solution so we can understand the problem.
    2. Your appreciation is accepted by clicking the star "Add Reputation" at the lower left of the post.
    3. If your problem is solved, mark it as [SOLVED]: See "FAQ : " https://www.excelforum.com/faq.php
    4. Use [CODE] [/CODE] tags to illustrate your code: see here

  3. #3
    Registered User
    Join Date
    07-13-2018
    Location
    Liverpool
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Excel VBA Ranges & Rows

    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.

  4. #4
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: Excel VBA Ranges & Rows

    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?

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel VBA Ranges & Rows

    Have you checked what the value of LastRow is?
    If posting code please use code tags, see here.

  6. #6
    Registered User
    Join Date
    07-13-2018
    Location
    Liverpool
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Excel VBA Ranges & Rows

    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
    Last edited by S1LV3RF0X87; 07-13-2018 at 06:41 AM.

  7. #7
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: Excel VBA Ranges & Rows

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

  8. #8
    Registered User
    Join Date
    07-13-2018
    Location
    Liverpool
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Excel VBA Ranges & Rows

    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

  9. #9
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: Excel VBA Ranges & Rows

    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
    Last edited by MarMo; 07-13-2018 at 07:03 AM.

  10. #10
    Registered User
    Join Date
    07-13-2018
    Location
    Liverpool
    MS-Off Ver
    Office 365
    Posts
    8

    Post Re: Excel VBA Ranges & Rows

    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?

  11. #11
    Forum Contributor
    Join Date
    09-21-2009
    Location
    Belgium - Mechelen
    MS-Off Ver
    Office 365 - version 2310
    Posts
    278

    Re: Excel VBA Ranges & Rows

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 06-30-2016, 07:07 PM
  2. [SOLVED] Select Rows between 2 ranges
    By tbrozovich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2015, 11:29 AM
  3. Export Excel Ranges to Word bookmarks using VBA then save word doc based on excel ranges
    By CuddlyGoldfish in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-30-2013, 07:47 PM
  4. How to sum rows of varying ranges?
    By lbroxterman in forum Excel General
    Replies: 9
    Last Post: 07-25-2012, 10:28 PM
  5. Overlapping ranges different rows
    By alexo in forum Excel General
    Replies: 1
    Last Post: 10-20-2010, 12:39 PM
  6. Find ranges of rows that contain 1
    By admanirv in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-26-2005, 08:11 AM
  7. [SOLVED] auto add rows to ranges
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1