Column not offsetting

Question

Sub Delete_Columns()
    Dim Last_Row As Integer
    Dim rnge As Range
    Dim celladdres As Variant
    Dim v As Integer

    Last_Row = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

    Cells(Last_Row, [13]).Value = "Sampling"
    Range("C3").Copy Range("C" & Last_Row)
    Range("B3").Copy Range("B" & Last_Row)
    Range("A3").Copy Range("A" & Last_Row)

    Set rnge = Range("G3:G1000").Find(what:="Description")
    rnge.Find what:="Description"

    celladdres = rnge.Offset(-1, 30).Address
    Range("a2", [celladdress]).Delete
End Sub

Hi im trying to offset the column and the row when the description if found to delete all the data above from a previous test but when i offset it the row is moving up but the column isnt offsetting the 30 its suppose to. its not cmoin up with any errors. Do i need to set a letter as a value to get this to work? thanks max

'rnge.Find what:="Description" '.... This line does the same thing as the earlier one ?? — simple-solution, Sep 04 '20 at 10:45
Use "Option explicit" to avoid wrong variable names: celladdres vs. celladdress !! — simple-solution, Sep 04 '20 at 10:47
Declare your variables as `Long` instead of `Integer` Excel has for example more rows than `Integer` can handle! Since there is no benefit in using `Integer` you can [always use `Long` instead](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). — Pᴇʜ, Sep 04 '20 at 11:03
Note that `Range("a2", [celladdress]).Delete` does not do what you think. The `[ ]` has nothing to do with the `[A1]` notation. It must be `Range("a2", celladdres).Delete`. — Pᴇʜ, Sep 04 '20 at 11:07
Thank you for help Simple-solution and PEH ill try these both and see which one works best. — Max Murrell, Sep 04 '20 at 11:20

Pᴇʜ · Answer 1 · 2020-09-05T10:25:44.840

Make sure you use Option Explicit to detect variable typos.
Make sure every Range, Cells, Rows and Colums object has a worksheet referenced like ws.Range, otherwise it will take whatever sheet is active (and that can easily change by a user click).
As you can see in the documentation of the Range.Find method it is absolutely secessary that you specify the folowing 4 parameters or you get random results:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Without defining these parameters it might work now and stop working another time.
After using the Find() method make sure you test if something was found If Not FoundAt Is Nothing Then or it will erorr.
The [ ] you use in [celladdress] have nothing to do with the [A1] notation and do not work like you assumed. They need to be removed!
Declare your variables as close as possible to their first use instead of the very top. Otherwise you will easily end up with something like Dim v As Integer and never using v in the entire code.
Finally use proper formatting of the code and proper indentation. The easier the code is readable the less errors you will make and the easier it is to debug. Don't work like "I will fix formatting later". This will slow you down in writing good code and you probably will never fix it.

So you end up with something like:

Option Explicit

Public Sub Example()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1") 'define your sheet
     
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

    ws.Cells(LastRow, 13).Value = "Sampling"
    ws.Range("C3").Copy ws.Range("C" & LastRow)
    ws.Range("B3").Copy ws.Range("B" & LastRow)
    ws.Range("A3").Copy ws.Range("A" & LastRow)
     
    Dim FoundAt As Range 'define ALL these parameters below to prevent random/wrong results
    Set FoundAt = ws.Range("G3:G1000").Find(What:="Description", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchByte:=False)
    
    If Not FoundAt Is Nothing Then 'test if something was found you cannot delete if you found nothing
        Dim CellAddress As String
        CellAddress = FoundAt.Offset(-1, 30).Address
        ws.Range("A2", CellAddress).Delete
    Else
        MsgBox "'Description' was not found.", vbCritical
    End If
End Sub

score 1 · Accepted Answer · answered Sep 04 '20 at 12:15

Sampling

With that little info I could only come up with this. The comments should help you to change if something was misunderstood.

The Code

Option Explicit

Sub Sampling()
    
    ' Define worksheet.
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Calculate 'NewRow', the row below last non-blank cell in column "A".
    Dim NewRow As Long
    NewRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0).Row
    
    ' Write "Sampling" in column "M" of 'NewRow'.
    ws.Range("M" & NewRow).Value = "Sampling"
    ' Copy range "A3:C3" to the same columns in 'NewRow'.
    ws.Range("A3:C3").Copy ws.Range("A" & NewRow, "C" & NewRow)

    ' Find the first occurrence of "Description" in column "G"
    ' starting from the 3rd row and ending above 'NewRow'.
    Dim rng As Range
    Set rng = ws.Range("G3", "G" & NewRow - 1) _
                .Find(What:="Description", After:=ws.Range("G" & NewRow - 1), _
                      Lookin:= xlValues, LookAt:=xlWhole)
    ' Check if "Description" was not found.
    If rng Is Nothing Then Exit Sub
        
    ' Delete range 'A2:AK2' resized to the row above of
    ' where "Description" was found.
    ' First test with 'Select'.
    ' When tested, replace 'Select' with 'Delete'.
    ws.Range("A2", "AK" & rng.Row - 1).Select
              
End Sub

Column not offsetting

2 Answers2

Sampling