Substitute variable into a range

[alliejane]

VBA Newbie here.

I have a routine that returns the first open cell in a column - and have set COUNT (defined as an interger) to that value, for example: COUNT = 898

Later on, I need to use that variable as the end of a range when autofilling cells. Right now, it says:



Selection.AutoFill Destination:=ActiveCell.Range("A1:E1000")


How do I use count instead? I'd like the range to be A1:E898, but each spreadsheet will have a different count value, so I would rather use COUNT instead of a number

[Simon Lloyd]

Try this:


Selection.AutoFill Destination:=Range("A1:E" & COUNT)

[lucas]

Hi alliejane,
can you tell us which column has the data you wish to check for last row?
I can offer this macro which checks column D for the lastrow and adds a formula in column E to the last row found in column D:
Option Explicit
Sub add_formulas()
Dim test As Boolean, x As Long, lastrow As Long, col As Long
Dim FillRange As Range
Range("D1").Select
col = ActiveCell.Column
lastrow = Cells(65536, col).End(xlUp).Row
For x = lastrow To 1 Step -1
test = Cells(x, col).Text <> ""
Range("E1").Select
ActiveCell.Formula = "=SUM(C2:D2)"

Range("D1").Select
Set FillRange = ActiveCell
On Error GoTo 0
If FillRange Is Nothing Then
Exit Sub
End If
Set FillRange = FillRange(1, 2)
lastrow = Cells(65536, ActiveCell.Column).End(xlUp).Row
If lastrow > FillRange.Row Then
FillRange.AutoFill Range(FillRange.Address & ":" & _
Cells(lastrow, FillRange.Column).Address), xlFillDefault
End If
Next
End Sub

[alliejane]

worked great! thank you!!!

[lucas]

guess I took too long...and too long winded...

[alliejane]

Lucas,

Column A is what I'm trying to check against. I think that I'm going to try your suggestion as I know how I have it written right now is totally inefficient.

I'm sure this is another can't believe someone is asking something so simple question....I've set it up to autofilter, looking for specific criteria. If that criteria is there, I highlight the rows. Since COUNT is an interger, I thought that I could try putting it in this statement -- and it didn't work. Suggestions?

ActiveCell.Offset(1, 0).Rows("1:Count").EntireRow.Select

[lucas]

This will highlight the entire row if the word yes is found in column A. Change the word yes in the code to your criteria:
Option Explicit
Public Sub HighlightRows()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, "A").Value = "yes" Then
.Rows(i).Interior.Color = vbYellow
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[unmarkedhelicopter]

You may need to change :
ActiveCell.Offset(1, 0).Rows("1:Count").EntireRow.Select
to
ActiveCell.Offset(1, 0).Rows("1:" & Count).EntireRow.Select

if you put a variable within quotes it's just text ;)

[daniel_d_n_r]

count = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

not sure if it will help but might be useful in future ..

[Norie]

Why do you need code to highlight specific data?

Can't you just use Format>Conditional Formatting...?