select range vba macro should select next 15 rows from lastrow and the last row is identified

Question

Original title: select range

Hi,

I need a excel vba macro to select 15 rows and on every refresh of the macro the range should select next 15 rows from lastrow and the last row is identified

with error #REF!, the reason of the error is i have copied getpivot function to all my cells, so that next time when my pivot are refreshed the #REF! cells will

pick the data accordingly.

example:

ColumnA have a formula from A131 to A155(end is not fixed), my first 15 range will be Range (A131:A145),

assume that A146 is #REF! next day when i refresh the pivot the #REF! of cell A146 will have data so my range of next day will be next 15 that is (A132:A146)

A131

A132

A133

A134

A135

A136

A137

A138

A139

A140

A141

A142

A143

A144

A145

A146

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

my below code will work only if there a last row, but in my tabel there is no lastrow because the last row is full of erorrs

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("b" & lastrow).Offset(-14, 0).Resize(15, 1).Select

Answer 1

Both above code is saying no cell found, when already there are cells with error

It would appear that your #REF! errors are actually text representations of errors or error values that are not produced by formulas. Try this modification to my routine that searches for #REF! error values,

Sub mcrLastNonError()

    Dim lastrow As Long, e As Long, selectsz As Long

    selectsz = 15  'try for a maximum of 15 rows

    lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For e = lastrow To 1 Step -1

       If Not WorksheetFunction.IsError(Range("B" & e)) Then Exit For

    Next e

    Range("B" & e - Application.Min(e, selectsz) + 1). Resize(Application.Min(e, selectsz), 1).Select

End Sub

Answer 2

Does this line of code work for you...

LastRow = Split(Columns("A").SpecialCells(xlFormulas, xlTextValues).Address, "$")(4)

Answer 3

Both above code is saying no cell found, when already there are cells with error

Answer 4

···

#REF!

my below code will work only if there a last row, but in my tabel there is no lastrow because the last row is full of erorrs

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("b" & lastrow).Offset(-14, 0).Resize(15, 1).Select 

You will need to text the lastcell for an Excel function error and walk the selection point up the column until either no error or the first row is found. Try this modification,

Sub mcrLastNonError()

    Dim lastrow As Long, e As Long

    lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For e = lastrow To 1 Step -1

       If Intersect(Range("B" & e), Cells.SpecialCells(xlFormulas, xlErrors)) _

            Is Nothing Then Exit For

    Next e

    lastrow = Application.Max(e, 15)

    Range("B" & lastrow).Offset(-14, 0).Resize(15, 1).Select

End Sub

Note that I've controlled the selection point by making it a maximum of 15 so the resize will never generate an error. It would also be possible to modify the -14 in Offset() and the 15 in Resize() to accomplish the same error control.

Answer 5

Hi,

 

I need a excel vba macro to select 15 rows and on every refresh of the macro the range should select next 15 rows from lastrow and the last row is identified

with error #REF!, the reason of the error is i have copied getpivot function to all my cells, so that next time when my pivot are refreshed the #REF! cells will

pick the data accordingly.

 

example:

ColumnA have a formula from A131 to A155(end is not fixed), my first 15 range will be Range (A131:A145),

assume that A146 is #REF! next day when i refresh the pivot the #REF! of cell A146 will have data so my range of next day will be next 15 that is (A132:A146)

A131

A132

A133

A134

A135

A136

A137

A138

A139

A140

A141

A142

A143

A144

A145

A146

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

#REF!

my below code will work only if there a last row, but in my tabel there is no lastrow because the last row is full of erorrs

lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Range("b" & lastrow).Offset(-14, 0).Resize(15, 1).Select

 

 

Try adding this test on errors in the lastrow cell in column B:

If WorksheetFunction.IsError(Cells(lastrow, "B").Value) Then

  lastrow = ActiveSheet.Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors)(1).Row - 1

End If

If you know that there is always at least one error cell at the bottom, you can simply replace your code with this:

lastrow = ActiveSheet.Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors)(1).Row - 1

Range("b" & lastrow).Offset(-14, 0).Resize(15, 1).Select

Hope this helps / Lars-Åke