Excel Ranges

[simoncpage]

Hi, this is a simple example, I have a sheet with ranges (in columns)

Range_a1...to...Range_a250
Range_b1...to...Range_b250
..
Range_z1...to...Range_z250

(where 250 is the maximum number of rows showing - this could be anything from 1 to 250)

each row is link to its own sheet tab. What I want to do is to be able to delete a row and which is fine. But when I run functions loops 'for i = 0 to 123' then this messes up if a number of the rows have been deleted (say for instance if the row 14, 15, 16 had been deleted).

Any ideas on how to get round this. I initally worked on a shift values about but then this involves changing the values on the corresponding tabs which just got messy.

Any ideas would be appreciated

Thanks
Simon

 

[SkipVought]

simon,

Is the problem that your loop limit is wrong or is it that cell references are messed up when you delete a row?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[simoncpage]

Hi Skip

Well

1. yes the limit loops is wrong as in the above example it would need to skip ranges that correspond to rows 14, 15, 16 as these dont exist now. I guess I could use a on error resume next but I want a more elegant way as I tried to avoid that. And how do I get the upper limit? what I would need is a procedure that can count the ranges in the sheet that arent hidden. (n.b there are blank title row in between the rows with name ranges)

2. The cell references aren't messed up as the linking tab and the corresponding rows in the calculation sheets are also deleted.

 

[SkipVought]

You need to calculate the limits of your loop.

One technique is to use the End Method in a column that ALWAYS has a data value in every row.

The LAST row of data in Col A is...

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

Cells.Rows.Count = the number of rows in a worksheet = 65526

As far as rows without data -- I try to avoid since I use Table/List preinciples which prohibits empty rows or columns within a Table/List. So there you test for the exception.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[simoncpage]

See this is a problem too because unlike a "standard database format" there are no 1 columns that have to contain some data. So I wouldn't know which column to look up.

So not knowing which column to look up means that I wouldnt be able to look up end rows. See if the maximum name range goes up to 123 and there are only 120 columns showing im not sure how to tell it that 3 rows have been deleted and the limit is actually 123 (with resume next erroring)???

 

[SkipVought]

Another technique is to test all the rows and columns for the largest lastrow. This function returns the address of the row/col with largest row value

Function FindLastCell() As String
    Dim lRow As Long, lCol As Integer, mRow As Long, mCol As Integer
    lCol = ActiveSheet.UsedRange.Columns.Count
    mRow = 0
    For i = 1 To lCol
        lRow = Range(Cells(65536, i), Cells(65536, i)).End(xlUp).Row
        If lRow > mRow Then
            mRow = lRow
            mCol = i
        Else
        End If
    Next i
    FindLastCell = Range(Cells(mRow, mCol), Cells(mRow, mCol)).Address
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[simoncpage]

not sure that is going to work...if you think about say I had 200 rows and then I deleted the first 198 row

..then I used the last row function thingy and it would find that there were 2 rows. It doesnt tell me what the name range is i.e that the name ranges 199 and 200 are the two ranges which now exist...and hence my for loop on the cells would be for i = 199 to 200.....

....do you get what I mean?

 

[SkipVought]

Well simon,you keep changing the requirements. You were NOT asking about the FIRST row in a range, but the LAST row in a range.

A resourceful guy ought to be able to take the FindLastCell function and say to himself, "Simon, I wonder if I could 1) try to understand some of the principles illustrated here and 2) having grasped the concept, take on the challenge of modifying the code and testing the code to FindFirstCell so that I could make my ranges truly dynamic!"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[simoncpage]

Thats really helpful thanks