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
=========================================================================================================================================
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,003
Messages
5,834,825
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top