Need macro to clear header that appears throughout worksheet

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
The header looks like this

=========================================================================================================================================
ACCT NUMBER ACCOUNT NAME ENDING BAL ACTIVITY Y-T-D BAL DEBITS CREDITS CUMULATIVE TOTAL
=========================================================================================================================================
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Is this header all in one cell in a column, or is it parsed across the row? ie will
ACCT NUMBER ACCOUNT NAME ENDING BAL ACTIVITY Y-T-D BAL DEBITS CREDITS CUMULATIVE TOTAL
be in one cell or only
ACCT NUMBER

Which column will house the data?


Tony
 

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
Appears in Column B1 and B3 (Now this is embeded through out the document). Hope this help
=========================================================================================================================================

Column B2: ACCT NUMBER

Column G2: ACCOUNT NAME

Column H2: ENDING BAL

Column I2: ACTIVITY

Column J2: Y-T-D BAL

Column K2: DEBITS

Column L2: CREDITS

Column O2: CUMULATIVE TOTAL
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

This should get you started.
Code:
Sub ddd()
 With Range("B:B")
  Set c = .Find(what:="ACCT NUMBER")
  If Not c Is Nothing Then
   Do
    Rows(c.Row - 1 & ":" & c.Row + 1).EntireRow.Delete
    Set c = .Find(what:="ACCT NUMBER")
   Loop Until c Is Nothing
  End If
 End With
End Sub

Make sure you have kept a copy of the source data until you have tested it.

Tony
 

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
Thank you it worked!!! Not sure if you got this message,

one more line that is showing up that need to be deleted....

Column B1044 *** AMOUNT OUT OF BALANCE ***

Is this the correct script?

With Range("B:B")
Set c = .Find(what:="AMOUNT OUT OF BALANCE")
Loop Until c Is Nothing
End If
End With

I'm new at this and I hoping that I can pick it up.

Once again thank you for your help.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

If it is just a single line (no repeats) then you could do

Set c = Range("B:B").Find(what:="AMOUNT OUT OF BALANCE")
if not c is nothing then
cells(c.row,"A").entirerow.delete
end if

If you are entirely sure that it will always appear then this could be cut down to

Range("B:B").Find(what:="AMOUNT OUT OF BALANCE").entirerow.delete


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top