VBA code to select a range

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,164
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What is the vba code to select a range from a4 down until there is a free line?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
Hi dpaton05,

This is for Sheet1 (change to suit):

VBA Code:
Sheets("Sheet1").Range("A4", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Select

Regards,

Robert
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
You're welcome :cool:

The above macro will select the last row used (has data in it) in Col. A from A4. If you want the first blank cell (has no data in it) in Col. A from A4 you could use this:

VBA Code:
Option Explicit
Sub Macro2()

    Dim lngLastRow As Long
    Dim rngMyCell As Range
   
    Application.ScreenUpdating = False
   
    With Sheets("Sheet1")
        lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For Each rngMyCell In .Range("A4:A" & lngLastRow)
            If Len(rngMyCell) = 0 Then
                .Range("A4:A" & rngMyCell.Row).Select
                Exit For
            End If
        Next rngMyCell
    End With
   
    Application.ScreenUpdating = True
  
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,347
Messages
5,635,764
Members
416,879
Latest member
Excel_Newbie4980

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top