Basic VBA code question and how to implant it - Excel 2013

[2avrham]

Hi,

I usually record my macros and therefore I not really know how to use VBA code.

I have data file that include several rows that I don't need (quantity of the rows may changed from time to time).

see file example:

"
txtSelectedTimeSpan textbox8
Site: HVC Tester Type: CMT ,OLB ,Verigy

textbox12 textbox15 textbox21 textbox24 textbox30 textbox33 textbox36 textbox45 textbox46 textbox47 textbox48 textbox49
HVC CMT 93.75 76.84% 15.75 12.91% 4.5 3.69% 8 6.56% 0 0.00%
HVC Verigy 34.5 28.28% 43.75 35.86% 43.75 35.86% 0 0.00% 0 0.00%

textbox44 textbox4 textbox6 textbox11 textbox14 textbox17 textbox20 textbox23 textbox26 textbox29 textbox32 textbox35
CMT CMT0263 39.25 64.34% 10.5 17.21% 3.25 5.33% 8 13.11% 0 0.00%
CMT CMT0371 54.5 89.34% 5.25 8.61% 1.25 2.05% 0 0.00% 0 0.00%
Verigy ilv93k33 10.75 17.62% 38.5 63.11% 11.75 19.26% 0 0.00% 0 0.00%
Verigy ilv93k34 23.75 38.93% 5.25 8.61% 32 52.46% 0 0.00% 0 0.00%

textbox69 textbox70 textbox71 textbox72 textbox73 textbox74 textbox76
Feb 24 2015 1:00AM CMT CMT0263 UNDEFINED Down
Feb 24 2015 1:00AM Verigy ilv93k33 UNDEFINED Logged Out
Feb 24 2015 1:00AM DBELKIN Verigy ilv93k34 UNDEFINED Active
Feb 24 2015 1:00AM GDLUSER CMT CMT0371 alpineridge Tester Maintenance Active
Feb 24 2015 1:15AM CMT CMT0263 UNDEFINED Down

"

what im asking is how can I delete through code all row that above "textbox69" and cause to "textbox69" cell to be at cell A1 all the time.

in addition can I "install" this VBA code in PowerPivot? if yes how can I do it.

thanks a lot!!

 

[2avrham]

Only Vba code may help too.. thanks,

 

[MARK858]

Have a look at my signature block for some links on how to show screenshots.
In particular MrExcel HTML Maker or Excel Jeanie.

in addition can I "install" this VBA code in PowerPivot? if yes how can I do it.

I don't understand this question PowerPivot is an add-in, your macro's go in Excel.

 

[2avrham]

I will be more specific:

1. I need help to write VBA code that search for specific test "textbox69" and delete all rows above it.

2. my second question is if I can use this VBA code at PowerPivot table?

 

[MARK858]

I will be more specific:

I need help to write VBA code that search for specific test "textbox69" and delete all rows above it.

Not that specific...

Where is "textbox69" located? Column "A", Column "B", any Column?
Do you have a header row? If yes does this also get deleted?

 

[2avrham]

sorry...

"textbox69" always may be found at column A (row number is changing from time to time).

from my side all above data can be deleted including header.

 

[MARK858]

To delete rows above "textbox69" (with out the quotes and without blank spaces and any other text) in column A try the code below, test on a copy of your data because you are deleting then post back what you need to do with the powerpivot.

Sub deletex()
    Dim x As Long
    x = Columns("A:A").Find(What:="textbox69", After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Row - 1
    Rows("1:" & x).Delete
End Sub

 

[2avrham]

thanks,

I will try it soon.