Range object: string longer than 255 characters

    Hello all,


    I was hoping someone may direct me in my search for a solution to a pretty simple problem. In my code I would like to (1) use a loop to build a string of rows that need to be hidden (figured this part), and then (2) use the string I've built to hide all the rows using Range().EntireRow.Hidden = True.


    The line of the code that returns an error is as follows (rowsToHide is a variable that I use in the loop to built a string of rows that need to be hidden):


    Code
    .Range(rowsToHide).EntireRow.Hidden = True


    The code works fine if the rowsToHide variable consists of a relatively small numbers of cells (e.g., if rowsToHide = "A1,A3,A8" the code would work just fine and will hide rows 1, 3 and 8). However, if the value of rowsToHide becomes something like "A2,A5,A6,A7,A9,A12,A13,A14,A16,A19,A20,A21,A23,A25,A28,A29,A30,A32, ...." (and so on and so forth) the code will return an error instead of hiding rows 2 5, 6, 7 etc..


    From what I can tell this is because the .Range() object is limited to 255 symbols. I was wondering if there's a workaround for this problem? Maybe, a different approach? I would very much like to build a string first and then hide all the rows as hiding them one by one would be very slow.

    Re: Range object: string longer than 255 characters


    Have you tried removing the letters from your string and using Rows(rowsToHide) instead of the range object? That would nearly half the length of your string.


    The other option is build an array of strings that are no greater than 255 in length and then iterate through your array to delete the rows.

    Re: Range object: string longer than 255 characters


    Thanks S O, these are excellent suggestions. I would imagine Rows() has the same 255 symbol limitation, right? I will look into an array solution, my problem is that I will likely exceed 255 limit pretty fast anyway, I'm looking at hundreds of rows that need to be hidden.

    Re: Range object: string longer than 255 characters


    Just use Union method.


    Like

    Code
    Dim rowsToHide As Range
Set rowsToHide=Application.Union(Range("A2,A5,A6,A7,A9,A12,A13,A14,A16,A19,A20,A21,A23"), _
Range("A2,A5,A6,A7,A9,A12,A13,A14,A16,A19,A20,A21,A23"), Range("and so on"),Range("and so forth"))
rowsToHide.EntireRow.Hidden = True

    Re: Range object: string longer than 255 characters


    jindon, this is a neat thick - thanks a lot for sharing! might be exactly what I need going forward.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login