How to select entire column except header-CodePudding

I am using below code.

Sub Replace_specific_value()
'declare variables
Dim ws As Worksheet
Dim xcell As Range
Dim Rng As Range
Dim newvalue As Long
Set ws = ActiveSheet
Set Rng = ws.Range("G2:G84449")
'check each cell in a specific range if the criteria is matching and replace it
For Each xcell In Rng
xcell = xcell.Value / 1024 / 1024 / 1024
Next xcell
End Sub

Here i don't want to specify G2:G84449 , how do i tell VBA to pick all value instead of specifying range?

CodePudding user response：

Watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)

Here is the standard way to get the used cell in column G starting at G2:

With ws
    Set Rng = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp))
End With

If the last row could be hidden use:

With ws
    Set Rng = Intersect(.Range("A1", .UsedRange).Columns("G").Offset(1), .UsedRange)
End With

If Not Rng Is Nothing Then
    'Do Something
End If

CodePudding user response：

Here's a slightly different approach that works for getting multiple columns, as long as your data ends on the same row:

set rng = application.Intersect(activesheet.usedrange, activesheet.usedrange.offset(1), range("G:G"))

This takes the intersection of the used range (the smallest rectangle that holds all data on the sheet, with the used range offset by one row (to exclude the header, with the columns you are interested in.

CodePudding user response：

Reference Column Data Range (w/o Headers)

If you know that the table data starts in the first row of column G, by using the Find method, you can use something like the following (of course you can use the more explicit
With ws.Range("G2:G" & ws.Rows.Count) instead, in the first With statement).

Option Explicit

Sub BytesToGigaBytes()
    
    Const Col As String = "G"
    
    Dim ws As Worksheet: Set ws = ActiveSheet 'improve!
    
    With ws.Columns(Col).Resize(ws.Rows.Count - 1).Offset(1) ' "G2:G1048576"
        Dim lCell As Range: Set lCell = .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' empty column
        With .Resize(lCell.Row - .Row   1) ' "G2:Glr"
            .Value = ws.Evaluate("IFERROR(IF(ISBLANK(" & .Address & "),""""," _
                & .Address & "/1024/1024/1024),"""")")
        End With
    End With

End Sub