Last Row from different column

[fddekker]

I would like to copy the content from cells above blank cells to subtotal rows. (In this example, columns A, C and D). The last row should be determined by Column B, which contains the filter for the subtotals.

There is something wrong with my syntax, and I can't figure it out.
My code is (for column A)

Sub Fill()


Dim Last_RowB As Long


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

Range("A" & Last_RowB).Select
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value

End Sub


Thanks in advance

 

[Norie]

The syntax of the code looks fine to me, are you getting any errors?

 

[Rick Rothstein]

The syntax of the code looks fine to me, are you getting any errors?

Sorry Norie, but I see problems with it.

My code is (for column A)

Sub Fill()
Dim Last_RowB As Long

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

Range("A" & Last_RowB).Select
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value

End Sub

You Dim'med, and specified in your Range call, the last row variable as Last_RowB, but you assigned the last row value to a variable named LastRow. Also, you selected the last cell and then tried to reference it as if it were in a With..EndWith block (using leading dots on SpecialCells and Value). Also, you make use of a variable named sht when assigning the last row but you never set a reference for that sht variable. You need to do it something like this (it is untested, but I think I got it right)...

Sub Fill()

  Dim Last_RowB As Long, Sht As Worksheet
  
  Sht = Sheets("[B][COLOR="#FF0000"]The Sheet Name Goes Here[/COLOR][/B]")
  Last_RowB = Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row
  
  With Range("A1:A" & Last_RowB)
    .SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
    .Value = .Value
  End With
  
End Sub

 

[Norie]

Oops, I really need to pay more attention and look closer.

 

[fddekker]

I renamed the tab Sheet1 and then used the code below, but it stills gives me an error at the sht row. What is the syntax if I only want to refer to the active sheet and not the sheet name using this code block. (Sorry for my oversight with Last_Row and Last_RowB, this is part of a bigger set of code.)

Many thanks for your help

Sub Fill()

Dim Last_RowB As Long, Sht As Worksheet

Sht = Sheets('Sheet1")
Last_RowB = Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row

With Range("A1:A" & Last_RowB)
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value
End With

End Sub

 

[Rick Rothstein]

I renamed the tab Sheet1 and then used the code below, but it stills gives me an error at the sht row. What is the syntax if I only want to refer to the active sheet and not the sheet name using this code block. (Sorry for my oversight with Last_Row and Last_RowB, this is part of a bigger set of code.)

Many thanks for your help

Sub Fill()

Dim Last_RowB As Long, Sht As Worksheet

Set Sht = Sheets('Sheet1")
Last_RowB = Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row

With Range("A1:A" & Last_RowB)
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value
End With

End Sub

Sorry, my fault... I forgot to include the Set keyword shown in red above.

 

[fddekker]

Got it! Thanks again.