1

I have a block of data like below that I need to insert into a sheet with several thousand rows.

enter image description here

The first catch is that some of the block is already there but incomplete. This is best illustrated with examples.

enter image description here

In this case, I am missing rows for 7010 and 7020.

enter image description here

In this case, I am only missing 7010. It’s all random. I could override the existing block but the second catch is that I have data in the cells to the right so the obvious solution is to insert blank rows corresponding to the missing numbers.

Because I know the block always start with a 7, I started doing something like (the data is in column B)

If Left(Cells(Row, 2), 1) = 7 Then
If Cells(Row, 2) = 7005 Then

Then realized that’s a quagmire of nested if statements. And then it gets trickier because after I insert the necessary row, the cycle repeats XD so I thought maybe I use an array to store the incomplete block and compare it to the complete block then insert rows that way but that’s also a nightmare. This is all made worse because I have to loop through the entire sheet and do this wherever necessary. I can code so if anyone can just provide logic, that will suffice.

If it’s easier, I do not need to fill in the newly inserted rows. Meaning I can just leave it all blank like below. Let me know if this makes sense or need more info.

enter image description here

Improve this question
11
  • Don't ask why I am doing this. I know it's obtuse but I don't have a choice XD Commented Jul 30, 2015 at 17:06
  • 4
    Actually it is pretty simple :) 1 Loop through the first block and check if it is there or not in the 2nd block. 2 If it is not then simply add it to the end of the block 3 Sort the 2nd complete block based on column 1 in the end. The blank rows on the right of the numbers will be automatically created :) Commented Jul 30, 2015 at 17:15
  • 1
    Whoa Sid. The man that started my SO journey! I feel honored ^_^; That's not bad at all. Figure out what's missing, add it then worry about order last. Will try that... Commented Jul 30, 2015 at 17:18
  • Hmmm what's the best way to do step 1? Block 2 doesn't always start with 7005. This is still very tedious XD Commented Jul 30, 2015 at 17:37
  • 1
    I will do better than that. i will not completely answer your question yet i will post enough to get you SPRINTING!!! Commented Jul 30, 2015 at 18:06

1 Answer 1

Reset to default
1

Actually it is pretty simple :)

Let me give you a hint with code examples which will not directly answer your question but will get you started!

Note: I am using hardcoded range for demonstration purpose.

  1. Loop through the first block and check if it is there or not in the 2nd block.

Example

Dim BlockA As Range, BlockB As Range
Dim aCell As Range

Set BlockA = Range("M1:M10")
Set BlockB = Range("A1:A100")

For Each aCell In BlockA
    If Application.WorksheetFunction.CountIf(BlockB, aCell.Value) > 0 Then
        Debug.Print "Match Found for " & aCell.Value
    Else
        Debug.Print "Match Not Found for " & aCell.Value
    End If
Next
  1. If it is not then simply add it to the end of the block

First find the last row using THIS and then simply increment it before writing to it.

Example

LastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row + 1
'~~> aCell is the cell which is not there in the 2nd block
Range("A" & LastRow).Value = aCell.Value
  1. Sort the 2nd complete block based on column 1 in the end. The blank rows on the right of the numbers will be automatically created :)

Example

Let's say your range is from A1:F100 then you can use this for sorting

With Sheet1
    .Columns("A:F").Sort Key1:=.Range("A2"), _
                         Order1:=xlAscending, _
                         Header:=xlYes, _
                         OrderCustom:=1, _
                         MatchCase:=False, _
                         Orientation:=xlTopToBottom, _
                         DataOption1:=xlSortNormal
End With
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Gah you beat me. Kind of. I apologize. I was not clear. There are 4743 rows while each blockB only ranges from 5 to 100ish. So I don't want the lastrow of the sheet but lastrow of the current block. The sheet is also already sorted so when I sort, I need to only sort the current block as well. Although I could do a mass sort with multiple criteria at the very end. Still +1 and will DEFINITELY do this next year!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.