How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36053
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

i have a report that i convert to excel sheet each week. It

This answer was rated:

i have a report that i convert to excel sheet each week. It has a page title that i manually highlight and delete for each page of the report. What i need is a macro or some other method to delete the page title while leaving the blank lines between the data. Is this an easy fix? I've tried several methods of sorting but have not had any success.
Thank you for your question, my name is Richard.

Can I look at this sheet please?

- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Thank you
Customer: replied 3 years ago.


540228 report id

Thanks

this is the Macro you need

Sub JA()

Dim i As Integer
On Error Resume Next
i = 1

While i <= 10000

If Left(Range("A" & i), 1) = " " Or Left(Range("A" & i), 4) = "Page" Or Asc(Left(Range("A" & i), 1)) = "12" Then
Rows(i & ":" & i) = ""
End If
i = i + 1
Wend


End Sub


If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Customer: replied 3 years ago.


So far i think it worls well but it doesn't delete the header lines, it blanks them out and leaves a gap in the data where the header use to be. Is there a way to delete the lines?

Of course

Use this Macro

Sub JA()

Dim i As Integer
On Error Resume Next
i = 1

While i <= 10000

If Left(Range("A" & i), 1) = " " Or Left(Range("A" & i), 4) = "Page" Or Asc(Left(Range("A" & i), 1)) = "12" Then
Rows(i & ":" & i).delete
End If
i = i + 1
Wend


End Sub


Let me know if you have any difficulties please

Customer: replied 3 years ago.


This eliminates the the blank lines between the data as well as the header lines. Can it select only lines that contain "Page" or "A.7" in the text?

ok, so if it has Page or A.7 is should delete the line

Else it just clears the text?

Customer: replied 3 years ago.


yes that would work

This does it

Sub JA()

Dim i As Integer
On Error Resume Next
i = 1

While i 0 Or Left(Range("A" & i), 4) = "Page" Then
Rows(i & ":" & i).Delete
i = i - 1
ElseIf Left(Range("A" & i), 1) = " " Or Asc(Left(Range("A" & i), 1)) = "12" Then
Rows(i & ":" & i) = ""
End If
i = i + 1
Wend


End Sub

Let me know if you have any difficulties please

use this code sorry

Sub JA()

Dim i As Integer
On Error Resume Next
i = 1

While i 0 Or Left(Range("A" & i), 4) = "Page" Then
Rows(i & ":" & i).Delete
i = i - 1
ElseIf Left(Range("A" & i), 1) = " " Or Asc(Left(Range("A" & i), 1)) = "12" Then
Rows(i & ":" & i) = ""
End If
i = i + 1
Wend


End Sub
Customer: replied 3 years ago.


getting Syntax error on the line that begins While

The editor took out some of the code.

I have uploaded it for you to copy

Go to www.wikisend.com

Click download and use file id 734912

Then use that code please

Customer: replied 3 years ago.


Works great! Thanks!!

Your welcome!

If you have continued problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Richard and 7 other Microsoft Office Specialists are ready to help you

Related Microsoft Office Questions