Selecting the first row (variable) in my table using a macro

[ottosen]

Hi,

I've made a macro and spend some time making it work across different sizes of data. The last issue I'm having is after the macro sorted out some data, that it will then delete, it has to select the first row and shift select down before deleting it. However, depending on how the data is sorted, the first row could be 9, could be 10 and so on. See below for my code.

Sub Macro1()
    Windows("statistik.xls").Activate
    Range("A8").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, Selection.SpecialCells(xlLastCell)), , xlYes).Name _
        = "Table1"
    Range("Table1").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:= _
        Array("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), Operator _
        :=xlFilterValues
    Rows("9:9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
    Application.Goto Reference:="Table1"
    Selection.Copy
    Windows("Lead Template.xlsm").Activate
 '  ActiveSheet.Paste
End Sub

If the first row in my data set is not one of the 3 criteria ("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), then the first row # will be 10 instead of 9. If the first two rows are not one of the 3 criteria, then it would be row 11 and so forth. How do I make my macro take this into consideration?

Thanks for any help you might be able to provide!

Ottosen

 

[Andrew Poulsom]

See if you can incorporate the code in Post #10 here in your procedure:

http://www.mrexcel.com/forum/excel-...pplications-select-all-unhide-rows-table.html

 

[ottosen]

I'll give it a shot, but a lot of the stuff in that code is unknown to me. Basic stuff like dim etc, but google has saved me in the past.

For my macro, I used the recorder, and then fiddled around to make it do what I want.

 

[ottosen]

I ended up googling around a bit, and I made it work like this:

Sub ImportDataFromStatistik()
    Windows("statistik.xls").Activate
    Range("A8").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection, Selection.SpecialCells(xlLastCell)), , xlYes).Name _
        = "Table1"
    Range("Table1").Select
Dim RngToDelete As Range
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:= _
        Array("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), Operator _
        :=xlFilterValues
Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
   Selection.AutoFilter
   RngToDelete.Delete
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
    Application.Goto Reference:="Table1"
    Selection.Copy
    Windows("Lead Template.xlsm").Activate
    ActiveSheet.Paste
End Sub

Like I said, I'm not too familiar with the Dim function, but I see it as defining RngToDelete as Range. Does this mean that future use of range in that macro serves a different purpose than normally? That thought worries me a bit, since I do use Range later in the code.

 

[Andrew Poulsom]

The Dim statement is used to declare a variable (which you should always do). The As keyword is used to define the variable's data type (in your case a Range object). When declaring variables you should define their type if possible to preserve memory.