CODEWITHSUNDEEP
vbaexcel
Spartans
Spartans

Reputation: 13

Excel VBA - Finding a relative range of data

Bit of a VBA noob so please don't cringe too much at my code. I have a document with rows of data that always changes, and I wrote a small function to clear the previous entries before completing the rest of the macro. My goal is to clear all entries in Column H. I don't want to clear the entire column, just rows 6 and below. This works fine:

Range("H6", Range("H1048576").End(xlUp).Offset(1, 0)).Select
Selection.ClearContents

I then want to enter my formula that I placed in H6 for each row that has data in the same row in Column A. I paste values over the formula when finished also. I do this as follows:

    If Range("A7").Value <> "" Then
        Range("H6").Select
        Selection.AutoFill Destination:=Range("H6:H" & Range("A" & Rows.Count).End(xlUp).Row)
        Range("H6", Range("H1048576").End(xlUp)).Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If

The above all works fine. However, my issue is if I insert a column towards the front this breaks due to the absolute references. Searching around the site here showed me that I can find the value of my titles in row 5 as follows:

Dim MyRng As Range
Set MyRng = Range("5:5").Find("Column Title", LookIn:=xlValues, LookAt:=xlWhole)

But then I'm at a loss on how to do the rest of my code with the MyRng value. I'm guessing my backasswards way of going to the bottom of the sheet and jumping up to the last value to find the data range is the issue.

So once I find the column with the title in row 5, how do I set my range to be all the cells below it with data?

Thanks for the help!

Upvotes: 1

Views: 1544

Answers (1)

findwindow
findwindow

Reputation: 3153

Something like this:

Dim MyRng As Range
Dim lastRow As Long

With ActiveSheet

    lastRow = .Cells(.Rows.count, "A").End(xlUp).Row
    Set MyRng = .Range("5:5").Find("Column Title", LookIn:=xlValues, LookAt:=xlWhole)
    c = MyRng.Column
    Debug.Print c

    If .Range("A7").Value <> "" Then
         .Range("H6").Select
         Selection.AutoFill Destination:=.Range(.Cells(6, c), .Cells(lastRow, c))

         .Range("H6", .Range("H1048576").End(xlUp)).Select
         Selection.Copy
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
    End If

End With

Upvotes: 1

Related Questions