Last Row

[J.W. Aldridge]

I have info all the way from column A to HC, some columns down to row
500.

My formula is stopping wherever the data in column A is stops (row
264).

Any ideas on how to fix this?


Sub FillColBlanks_all()

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

Set wks = ActiveSheet

With wks

LastRow = Range("a500:hc500").End(xlUp).Row

Set rng = Nothing
On Error Resume Next
Set rng = .Range("A11:hc" &
LastRow).Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If


End With


End Sub


thanx

 

[Bob Flanagan]

how about:

with activesheet.usedrange
lastrow = .cells(.cells.count).row
end with

Note that there are periods in front of the the word "cells".

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Gary Keramidas]

you can use usedrange, but it's not always reliable. maybe a routing like this
before you run your code:

Sub Real_lastrow()
Dim lastcol As Long
Dim RealLastRow As Long
Dim ws As Worksheet
Dim arr As Variant
Dim i As Long
Set ws = Worksheets("Sheet1")
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)
For i = 1 To lastcol
arr(i) = ws.Cells(Rows.Count, i).End(xlUp).Row
Next
RealLastRow = Application.Max(arr)
End Sub

 

[Vergel Adriano]

Try replacing this line

LastRow = Range("a500:hc500").End(xlUp).Row


with this:

LastRow = wks.UsedRange.SpecialCells(xlCellTypeLastCell).Row

 

[Tom Ogilvy]

If you don't have only merged cells on your sheet:


Sub FillColBlanks_all()

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long

Set wks = ActiveSheet
LastRow = GetRealLastCell(wks).Row
With wks
Set rng = Nothing
On Error Resume Next
Set rng = .Range("A11:hc" & LastRow).Cells _
.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If


End With


End Sub

Public Function GetRealLastCell(sh As Worksheet) As Variant
Dim RealLastRow As Long
Dim RealLastColumn As Long
Set RealLastCell = sh.Range("A1")
On Error Resume Next
RealLastRow = _
sh.Cells.Find("*", sh.Range("A1"), _
, , xlByRows, xlPrevious).Row
RealLastColumn = _
sh.Cells.Find("*", sh.Range("A1"), _
, , xlByColumns, xlPrevious).Column
If Err.Number <> 0 Then bError = True
On Error GoTo 0
If bError Then
Set GetRealLastCell = sh.Range("A1")
Else
Set GetRealLastCell = sh.Cells(RealLastRow, RealLastColumn)
End If
End Function

 

[J.W. Aldridge]

Hi.

I tried implanting that bit into my code and it stills runs the macro
based on column A.

Any more suggestions? (Did I plug it in wrong?)


Sub FillColBlanks_all()


Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long


Set wks = ActiveSheet


'With wks

With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


LastRow = Range("a500:hc500").End(xlUp).Row


Set rng = Nothing
On Error Resume Next
Set rng = Range("A11:hd" &
LastRow).Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If


End With


End Sub

 

[J.W. Aldridge]

THANX ALL!

Each of the previous suggestions worked!