CODEWITHSUNDEEP
vbafindexcel-2007
Jason
Jason

Reputation: 1365

I am having trouble finding a column value and relating it to the corresponding row number in vba Excel2007

First, I am using Excel 2007, and working in the Visual Basic editor(VBA, I believe).

I am attempting to take a sheet of data(7500 rows), reduce it to 2999 rows and format it to begin at a InitialValue (0.2) from column "F", I want the InitialValue to be defined as the Row number. All values in all rows prior to the first occurrence of 0.2 in column "F" can then be deleted. Then I want to begin with the new row 1, and shorten the file to 2999 rows.

Column F is a data counter that sometimes resets due to a peculiar motion sensor input, and thus i would like to only find the first reference in the data set to the prescribed InitialValue of (0.2). I am uncertain if i need to code a strategy for this.

the sheet is of the format:

ROW  |    A        |   B   |    C    |        D
1       date-time    data      data        599.4
2       date-time    data      data        599.6
3       date-time    data      data        599.8
4       date-time    data      data          0.2
5       date-time    data      data          0.4
6       date-time    data      data          0.6
7       date-time    data      data          0.8
.       date-time    data      data          1.0
.       date-time    data      data          0.2
.       date-time    data      data          0.4
7500    date-time    data      data        345.4

I am fairly new to VBA, so i am surely missing something important here. I get an object required error at the "Set InitialValue" line

 Sub Scrub_clean()
        'Scrub_clean Macro  isolates event

    Dim InitialValue As Integer
    Set lookupTable = Worksheets("Sheet1").Range("D1:D7500")

    With Worksheets(1).Range("D1:D7500").Value
        Set InitialValue = .Find(0.2, LookIn:=xlValues)

    End With

    Rows("1:InitialValue-1").Select
        'subtract one from initial value to ensure the 0.2 value does not get deleted
    Selection.Delete Shift:=xlUp

    Rows("3000:7500").Select
    Selection.Delete
 End Sub

I am having no success with getting to the definition of the first occurrence of the 0.2 in column "D", and am not sure where to start to ensure the macro does not keep looking for other occurrences of that same value. Any help would be greatly appreciated. Thanks in advance.

Upvotes: 1

Views: 177

Answers (3)

Jason
Jason

Reputation: 1365

Thanks for your help. I ended up finding this to work as well, it is kind of crude, I wonder if the Find command would be a more efficient process? 

   Sub Scrub_clean()

    Dim InitialValue As Integer
    Set lookupTable = Worksheets("Sheet1").Range("d1:d7500")

    With Worksheets(1).Range("d1:d7500")
        Set InitialValue = .Find(0.2, LookIn:=xlValues)

    End With

    Rows("1:InitialValue-1").Select

    Selection.Delete Shift:=xlUp

    Rows("3000:7500").Select
    Selection.Delete
End Sub

Upvotes: 0

Reafidy
Reafidy

Reputation: 8431

You are trying to use the Set command on the InitialValue variable which is dimensioned as integer. You cant do that either use:

 InitialValue = .Find(0.2, LookIn:=xlValues).Row

Or dimension the InitialValue variable as a range, then you can use set like you originanlly did.

Also you don not need to select cells to work with them use:

Rows("3000:7500").Delete

This is how I would do what you request:

Sub Scrub_clean()
        'Scrub_clean Macro  isolates event

    Dim rCell As Range

    With Worksheets("Sheet1")
        Set rCell = .Range("D1:D7500").Find(0.2, LookIn:=xlValues)

        If Not rCell Is Nothing Then
            '// Assumes you have no headers as per your example
            .Rows(1).Resize(rCell.Row - 1).Delete Shift:=xlUp
            .Rows(3000).Resize(.UsedRange.Rows.Count).Delete Shift:=xlUp
        End If
    End With


 End Sub

Upvotes: 1

NoAlias
NoAlias

Reputation: 9193

Try removing .Value in your With statement. I don't think the Find function is a member of the Value property, but it is part of the Range object.

Upvotes: 0

Related Questions