Changing Excel cells from empty to null in preparation to import into Access

[rmccafferty]

I am stuck on an issue that revolves around the difference between a null cell versus an empty cell in Excel and Access.

The scenario is that we have people doing data entry in many tabs in an
Excel file, data which will then be imported into Access. In many of the tabs, we do not know in advance how many rows of data will be entered. The people are gathering information about buildings and every building is obviously different.

I must deal with a situation where the data input tabs are laid out in a manner that does not allow for importing into Access. So I added a somewhat matching set of tabs that rearrange the data in a way that will allow Access to read the data. That is, the data must have field names across the top row with the data in rows below it. And, of course, I often do not know exactly how many rows of data there will be.

I did this with formulas. In essence, I get data that in some cases needs to be transposed from vertical to horizontal layout. In other cases I need to get the data from a specific place in a tab where it is located in the middle of a lot of other clutter.

I have no options that allow me to change the layout in the data input tabs. And I have no option to arrange for the data to be input directly into Access, which would be my first choice.

I must deal with the data as it is currently laid out in the data input tabs in Excel.

So, here is what I have done thus far. I created a second set of tabs in Excel, using formulas that draw from the data input tabs, but which lay out the data in the required format of a header row for field names and rows of data below it. That is, the result is that the data looks like an Access table. I enclosed every formula in an IF statement that leaves the cells blank if the originating cell in the input tab was blank. The formula, roughly, is: =IF(OriginatingCell = “”,””, OriginatingCell).

I then run a macro that converts all formulas to values, leaving any unneeded/unused cells empty.

The problem in my process is that these cells that I designated as “” and then converted to values are not null and Access wants to import them all as blank entries rather than ignoring them. And we end up with lots of empty rows of data in Access. (We need to allow for receiving maybe as many as 20 rows of data in a tab, but in most cases end up with only a couple rows of data).

If I were doing this as a one time thing, I would just go into each tab, find the last row of data and deleted about 50 rows directly below the last line of data and be done with the problem. But there are too many spreadsheets and too many tabs per spreadsheet for this to be feasible, doing it all manually. Finding the last row of data with a macro is too prone to error. If there were a column where I absolutely positively knew would never be empty for a record, I assume that I could do the equivalent of End-DownArrow and deleted 50 rows below that. But in my experience, any field that “always” must have an entry, sooner or later, does not have an entry for what seemed like a valid reason to the guy making the entries, and then we wipe out a whole lot of data that we needed.

So, is there a way to convert cells that are empty into null cells? I would love, in code, to do the equivalent of:

For cells A1:P100, IF cell is empty, then cell becomes null, else do nothing.

I am self-taught in VBA and only so-so in it. I understand loops and IF statements, so I can do a lot with very limited knowledge, but am clueless with the less obvious and less common aspects of VBA.

Is there a way to do the above (making a cell be null) in Excel? Does anyone know of any other options that might work? Remembering that I have absolutely no options in terms of changing the data entry tabs or switching to Access for the data entry?

I understand that I could create queries in Access to delete all blank records, but that can be pretty clumsy and seems like not a great way to fix the problem.

I want to thank all in advance for any advice/solutions I am given.

 

[hatstand]

Hi, I had a similar problem with data being imported into Access. Excel is storing blank characters in the blank cells, so these need to be replaced with something else. Namley a 'real' blank. You can replace these characters with the code below. This piece of code is at the end of my data collection code.
This is what the blank characters are actually doing.

Chr(13) = Carriage Return - (moves cursor to lefttmost side)
Chr(10) = New Line (drops cursor down one line)

'-------------------------------------------------
sub ReplaceBlank()
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False

Range("A1").Select

Application.CutCopyMode = False
End Sub

 

[rmccafferty]

hatstand,

I took a stab at using your code and it did not do what I need. It may well be something I did, so let me tell my test, okay?

1. First I took a blank spreadsheet.
2. I entered the number 1 through 10 in cells A1 through A10.
3. In F1 I entered the formula =IF(A1=::,"",A1) (thus showing a 1 in the cell, the same thing that was in A1).
4. I copied F1 down through F13. The result, as anticipated was that F1 through F10 had the exact same values showing as in A1 through A10. And cells F11 through F13 returned no value, what looks to be an empty cell.
5. Then I ran my macro that does a selectAll, copy, Paste Special-Values.

The code for that macro is:
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

6. I went to A1 and pressed Control-End and, as expected, it took me to cell F13, not F10.
7. Then I ran a variation of the code you gave to me. As I ran it, it was as follows:

Range("A2:F15").Select
'Range(Selection, Selection.End(xlDown)).Select
'Range(Selection, Selection.End(xlToRight)).Select

Cells.Replace What:=Chr(13), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
Cells.Replace What:=Chr(10), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False

Range("A1").Select

Application.CutCopyMode = False

The main changes I made were to set the range manually rather than letting the code select the range. that is, I changed the first line, the Range Select, to cover a range rather than one cell. And then rem'd out the two lines that were to be used to determine the range we needed to address, which should in this case be A1 through F13.

7. Then I again went to A1 and pressed Control-End. This time, if cells F11 - F13 had been made null, I would expect to be taken to cell F10, the last cell with no visible entry in it. Instead, it took me to cell F13 again, indicating that these cells (F10 - F13) that we tried to make null were not being seen as null by Excel.

Any thoughts?

Robert

 

[hatstand]

Hi,

I would imagine that you have a different chr() hidden in your data. Mine just so happens to be 10 or 13. Here's a link to the chr() list that will be be in yours. i.e. 0 - 32 or 127. You can use =char(cell ref) to see what is in your hidden cells.

Ascii Table

Or you could use the Data / Text to columns / Fixed width / Finish. This should remove anything that shouldn't be there.

Do either of these solve it?

 

[Firefly2012]

So is the final row of your data based on column A? If so, just determine that and then Clear/Delete any subsequent rows:

Dim lngLastRow As Long

lngLastRow = Cells(Rows.Count,"A").End(xlUp).Row

Range("lngLastRow + 1 & ":" & Rows.Count).Clear

 

[rmccafferty]

First, I want to again give a thank you to those trying to help me.

Hatstand,
When I do a Control-end, it takes me to cell F13, which is an empty cell, but also the last cell that had a formula that was converted to values. Thus, the cell i would expect to be taken to if these cells did not go null.

In cell G13 I typed: =CHAR(F13). that returned #VALUE! in cell G13.


Firefly. Some version of what you suggest is what I consider my last resort. Just delete a bunch of rows below the last entry in a column which I know must always have an entry in it. My hesitation with that from the beginning is that whenever a user tells me what column absolutely positively will always have an entry, it turns out not to be correct. Plus, virtually every cell with a value really is the result of a formula. And a simple End-DownArrow takes me not to the last data entry, but to the last cell that once had a formula in it (and now appears as empty once we run the PasteSpecial-Values.

And, since I probably did not think to mention this, much of this whole thing stems from the fact that I do not know in advance just how many rows will be used in many of the tabs. The source of the data is examinations physical characteristics of commercial buildings. So any given building might have one heating system or several. One computer server or several, Etc.

Ultimately, I just cannot figure out how to find the last row of real data. What I planned to do, if I give up and try to delete a bunch or "empty" but not necessarily null rows is to search a column that "should" always have entries and find the first one with a value less than some value, either numeric or alpha, or something like that. And then delete some number of rows below that. I would probably pick a high number of rows to delete, maybe 50 or 100, just to be absolutely sure I never delete too few.

Firefly,I do not understand some of your code. Actually, I do not understand most of it.
In the setting of the variable, at first I thought you were doing the equivalent of End-DownArrow. But that is not it. Would you be willing to explain it to me?
In the row in which you set the range for the Clear command, I get the variable plus 1 (to set the first row), I get adding the colon, but i do not understand the Rows.Count portion.