A complex request...sorta.

[Panavision]

Hello all,

I have a VBA formula I need help in getting worked out. I figured if any forum could figure it out, you guys could (I've gotten 75% of my solutions from this board.) First, a little background:

I have a spreadsheet that needs to pull data from other raw output xls files and incorporate them into the reporting spreadsheet. I have successfully executed an ADO process to pull the data ranges I need from the raw data sheets and copy them into the reporting spreadsheet. I did this by adapting an example made by the super awesome Ron de Bruin and his Excel Automation page. However, I have hit a wall on incorporating a copy/paste routine based on very specific parameters. I will try to explain what is happening and then what I need as best I can.

The raw xls files are the result of being converted from pdfs containing table data. The conversion works fine and the data I need to harvest is properly put into rows and columns. However, on some conversions, certain cells get merged & centered, and the data shifts in certain areas one or two cells to the right. To best explain this, I will try to illustrate using the actual data:

MiscDep	Rfnd	PdOts	FBal	NetExp	Cntd	Ovr/Sht
10	5	3	0	2	1.5	-.5
10	5		3	0	2	1.5	-.5

<tbody>
</tbody>

What's happening is that the data needing to be organized into the reporting sheet is off by a cell halfway through the range because of the hiccup during the conversion process. What I need is a subroutine that checks for that blank cell (or ignores it if isn't blank), then selects a specified data range (not the rest of the row), cuts it, and pastes it back inline with the other data within a specific range. So, that first row of numbers is fine, they all match up. But the second row has that dag-blasted blank cell and throws everything off. I used identical numbers in both rows for illustration purposes. The actual data rows will always have different data.

This code must be VBA because it has to execute immediately after harvesting the data from the raw xls and will be pasted directly underneath the ADO code. I have to do it this way because the data being harvested from the raw xls sheet is generated daily and will be archived the next day. I'm not smart enough to figure out how to get the ADO process to look for column descriptions (not headers, these descriptions happen further down) and match data ranges underneath based on a descriptor and then pull them into the reporting sheet, so I simply coded it to pull the data on the extreme end the ranges to account for the shift and dump it directly into the reporting sheet where further subroutines will access it. To give you an example of what I mean, the following is the harvesting code, written by Ron de Bruin (targeting specific areas of the raw xls sheet):

Sub GetData1()
    Dim SaveDriveDir As String, MyPath As String
    Dim FName As Variant

    SaveDriveDir = CurDir
    MyPath = Application.DefaultFilePath
    ChDrive MyPath
    ChDir MyPath
    FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*")

    If FName = False Then
        
    Else
        GetData FName, "Table 1", "H34:J34", Sheets("03").Range("AD21"), False, False
        GetData FName, "Table 1", "K4:N4", Sheets("03").Range("AG21"), False, False
        GetData FName, "Table 1", "K5:N5", Sheets("03").Range("AO21"), False, False
        GetData FName, "Table 1", "C8:N8", Sheets("03").Range("AW21"), False, False
        GetData FName, "Table 1", "I9:N9", Sheets("03").Range("BI21"), False, False
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
End Sub

So as you can see, I'm targeting specific ranges in the raw xls, then dumping them into specific spots on the same row to aggregate the data I need onto one row, in a specific pattern. The reason I'm doing it this way is because the reporting sheet has to be used by a great deal of people who are not...overtly tech savvy. This code is designed to execute by a person simply hitting a button, selecting the raw file (there are hundreds of them), and having the results put into place for them. When the data is off by a row due to data conversion shifts, it won't get reported properly and will throw off many other equations in the reporting sheet.

I know this seems idiotic; me asking for a code like this. The thing is though, when I did a search for this type of "if x=blank then select/cut/paste" question, I was COMPLETELY inundated with "copy/paste based on cell value" results. And they all had very specific execution functions that I couldn't adapt (figure out) to my needs.

So...any help?

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Sub Test()
    With ActiveSheet.Range("A1").CurrentRegion
        .MergeCells = False
        .SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    End With
End Sub

 

[Panavision]

Hi Andrew,

Would the "A1" start with the blank cell?

Example:

Let's say my table up above started with A1. Would the range I input be "C3:H3"?

 

[Andrew Poulsom]

If your table starts in A1 Range("A1").CurrentRegion will return all of it. Then SpecialCells(xlCellTypeBlanks) will return only the blank cells, of which C3 is one.

 

[Panavision]

Well, that would cover too much real estate within the sheet. I need a targeted range to check a specific cell for a blank and then execute the code. If it searches for blank cells everywhere and then executes the code everywhere, that would wreak havoc.

 

[Panavision]

I figured it out. Here's the code:

With Range("AD22:BN22").Select
        Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    End With

It worked perfectly.

 

[Andrew Poulsom]

You don't need to select:

Range("AD22:BN22").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

Surely it's not just one row that contains the blanks?

 

[Panavision]

It's fifteen rows; but the execution was dependent on very specific parameters, and not all rows may used each time. It was vital that the code only be executed on those specific areas depending on the user's input.