Deleting Rows in a range if they contain certain text

[HellNoJello]

I want to delete all the rows in column A if they contain the text "FILENET TRANSMITTAL FORM" How can I do this?

Thanks.

 

[HellNoJello]

Let me reword - I want cells 8-14 to end up in b2, c2, d2, e2 and so on...then 15-21 to end up in b3, c3, d3, e3 and so on...

 

[Datsmart]

This does what you are asking:

Sub TransposeColumnA()
'Assign variables
SR = 8
ER = SR + 6
LR = Cells(Rows.Count, "A").End(xlUp).Row
FPR = 2 'First paste Row

Do Until ER > LR
    Range("A" & SR & ":A" & ER).Copy
    Range("B" & FPR).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A" & SR & ":A" & ER).ClearContents
    SR = SR + 7
    ER = SR + 6
    FPR = FPR + 1
Loop

End Sub

 

[erik.van.geit]

an alternative performing all operations "in memory"

Option Explicit
Option Base 1

Sub transpose()
'Erik Van Geit
'051102

Dim arr1 As Variant
Dim arr2 As Variant
Dim i As Long
Dim r As Long
Dim rng1 As Range
Dim rng2 As Range

'**** EDIT ****
Const col As Integer = 1        'column with source data
Set rng2 = Cells(1, 2)          'first cell new range
Const c As Integer = 7          'number of columns
'**** END EDIT ****

Set rng1 = Range(Cells(1, col), Cells(Rows.Count, col).End(xlUp))
r = Int(rng1.Count / c) + 1

ReDim arr2(r, c)
arr1 = rng1

For i = 1 To UBound(arr1)
arr2(Int((i - 1) / c) + 1, IIf(i Mod c, i Mod c, c)) = arr1(i, 1)
Next i

    With rng2
    .Resize(Rows.Count, c).ClearContents
    .Resize(r, c).Value = arr2
    End With

End Sub

 

[erik.van.geit]

erik

i'm trying to do something similar

i need to delete an entire row (not just Col A) if Col A contains "Colorado"

i'm trying to amend your code, but i thought i'd ask also

thanks

Mike

it does delete entire rows: it's written in almost plain english in the code itself
if you have more questions, please start a new thread, else this one will get confusing

best regards,
Erik

 

[Datsmart]

Eric,

I am working with your code and trying to understand how it works.
Noted that it does not list every seventh item from column A at all. Is that by design? Or am I missing something?
Also, it does not delete rows as you mentioned in your last post.