VBA code to insert blanks after two conditions

[Roooney]

Hi all,

Thank you if you can help me. I've been trying to write this for a couple of days.

I have a set of data that I copy and paste from the web that doesn't include blanks. So when I Power Query the data in try to Pivot the data it can go out to ZZZ on occassion.

The data looks like this in a table:

DATA TABLE
Last Name
Test
First Name
Test
User ID
Test
Type
Permanent
Class
Class 1
Effective Date
dd/mm/yyyy
Status
Active
Request type
Assess
Facility
Test
And it repeats...

However when you copy over and there are blanks under "Class" and "Request type" it won't copy over the blank. Can someone provide me a VBA code that will insert a blank row following these two conditions:

- If row contains "Request type" and next row doesn't say "Assess" or other words then add a blank row underneath; and

- if row contains "Class" and next row doesn't begin with "Class' then add a blank row.


Any help would be much appreciated.

Cheers,

 

[Joe4]

Welcome to the Board!

Your question isn't entirely clear. For example, you say:

- If row contains "Request type" and next row doesn't say "Assess" or other words then add a blank row underneath

Which column(s) are we looking in? Are we always just looking in column A?
And is "Request type" the FULL value in the cell, or are we just looking for the phrase "Request type" among other words in the cell (i.e. "This is a new Request type")?
And I do not understand "or other words". Are some cells blank already, or just have numbers in them?
It would be good to see BOTH conditions where we add and do not add blank rows.

I think the best thing to do would be to show us some sample data and expected results (you know what they say about a "picture saying 1000 words!").

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.