0

I have data that starts in B3 and is dynamic so its range may be 5 rows to 500. I want to loop through the number of rows in the range.

This is for a data query on number of report checked in.

Dim range1 As Range

Set range1 = Sheet1.Range("B3 : B" & Cells(Rows.Count)).End(xlUp).Rows

The result should be a range B3:B46 or whatever the end row number is.

GSerg
  • 76,472
  • 17
  • 159
  • 346

5 Answers5

1

Try this: 

 Dim cell As Range

    For Each cell In Sheet1.Range("B3:B" & Sheet1.Cells(Sheet1.Rows.count, 2).End(xlUp).Row)
        'Do your stuff
    Next cell
Guest
  • 430
  • 2
  • 4
0

Try this

Dim range1 As Range
Dim x as long

With Sheet1
    x= .Cells(.rows.Count, 2).End(xlUp).Row
    Set range1 = .Range("B3", .Cells(x,2))
End with
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Joel
  • 91
  • 7
0

Another way to do it that will give you the last row that has data in it:

Option Explicit
Sub RowCount()

    Dim WS As Worksheet
    Dim lrow As Long

    Set WS = ActiveSheet

    With WS
        lrow = .Range("B" & .Rows.Count).End(xlUp).Row - 2
    End With
End Sub
QuickSilver
  • 730
  • 5
  • 28
0

Simplest way is to use the .End(xlUp) property.

Dim rng as Range: Set rng = Sheet1.Range("B3:B" & Sheet1.Cells(Rows.Count, 2).End(xlUp).Row)

Now you have B3:B<last_row> stored in the variable rng

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
0

Hi I am having a 1004 Auto fill error: Selection.AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)

Below is my code:

Range("A1:V50000").Select
Range("D6").Activate
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "key"
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("G1").Select
Selection.Copy
Range("H1").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Customer ID"
Range("G2").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.FormulaR1C1 = _
    "=IFNA(IFNA(INDEX(NS!R2C1:R1048576C9,MATCH(ACHW!RC[6],NS!R2C5:R1048576C5,0),1),INDEX(Uniques!R2C1:R1048576C4,MATCH(ACHW!RC[6],Uniques!R2C2:R1048576C2,0),1)),"""")"
Selection.AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("ACHW").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ACHW").Sort.SortFields.Add2 Key:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row) _
    , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ACHW").Sort
    .SetRange Range("A:Y")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Columns("A:Y").EntireColumn.AutoFit
Columns("H:H").ColumnWidth = 31.43
Sheets("Import").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select

End Sub

user19372643
  • 1