Clear content below search dynamically

[whatitdo]

Hi

is it possible to make clearcontent dynamic such that it clears the content below a certain word search?

Thing is i get alot of data sent my way every month and i've made a makro which transfers all that data unto another workbook which then do the calculation etc.

problem is though at the end of every table sent (size varies) is put in an additional row i would like to have cleared, plus everything below that row, as that is the old data.

with the code:


Dim Found As Range

Set Found = Worksheets("Result").Cells.Find(What:="(tom)", LookAt:=xlWhole, MatchCase:=True)

If Not Found Is Nothing Then
ActiveCell.EntireRow.ClearContents


End If



i find the word/string (tom) in the sheet and clears it but that will not delete every row below and since the tables varies i cannot simply add a range. Any suggestions?

 

[dmt32]

Hi,
welcome to forum

try this update to your code & see if does what you want

    Dim Found As Range
    With Worksheets("Result")
        Set Found = .Cells.Find(What:="(tom)", LookAt:=xlWhole, MatchCase:=True)
        If Not Found Is Nothing Then .Range(Found, Found.End(xlDown)).EntireRow.Delete
    End With

Dave

 

[whatitdo]

Absolutely brilliant!

Thanks

 

[dmt32]

Absolutely brilliant!

Thanks

Most welcome, glad suggestion helped you.

Many thanks for feedback

Dave

 

[whatitdo]

I have a related question if anybody won't mind

I use this code to transfer my data from one workbook to another:


Windows("IK-periode 26.xlsm").Activate
Sheets("Result").Select
Range("B6:G6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Arbejdsfil v2 - IKS (TESTER).xlsm").Activate
Sheets("Result").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



What I need is the IK-periode 26 referencing/linking to be dynamic if possible… usually the next file have a similiar name (the number in the end usually change as it represents week number)
Is it possible to for example make a linking in the workbook (arbejdsfil v2) to the new workbook file from the desktop and then reference the cell with the link or something? Other options is welcome too

 

[dmt32]

Hi,
As this is different to your first question, suggest you start a new thread as likely to gain more responses.

Dave