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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,595
Messages
6,143,389
Members
450,484
Latest member
ChrisMac1

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
Back
Top