How can I ignore blank rows in this VB

**Ginge1267** · 11-13-2013, 07:18 AM

Hi Guys, I'm really hoping you can help. I'm a bit of a novice as visual basic goes but I'm trying to learn.
I'm using Ron de Bruin's code here and its almost there, I've been reading in I don't know how many forums and I just cant find the answer.

I'm trying to get the code to skip the blank rows in the group of spreadsheets I need to consolidate. Using Ron's code below, I've created export.xlsx from five data sheets book1, book2, book3, book4 and Timesheet IGW Week 45 2013.xlsx, these are exports from another workbook, the names are not important but I've attached the files for convenience.

In the file export.xlsx you will see a lot of lines with #N/A and 0 that I am trying to filter out in the consolidation process.
Column C is hidden in the data sheets I'm consolidating from so I'd like to avoid that all together if possible. Also of note is that cells b11:b28 and d11:d28 are data validation cells from the original master sheet.

Here's my thinking:-
In the section "Set sourceRange = .Range("b8:t30")" , could I use "Set sourceRange = .Range("b8:b30, d8:t30")" to avoid column C?
I tried using "LastRow" but the debug kept stopping there so not sure what I was doing wrong.

Any help or advice would be greatly appreciated.

Ginge.

Ron's code, with paths edited to suit my system.

Please Login or Register  to view this content.

**Ginge1267** · 11-13-2013, 02:26 PM

Please can anyone help?

**Ginge1267** · 11-14-2013, 05:09 AM

Please can anyone help? Have I not explained it very well?

**Ginge1267** · 11-14-2013, 05:56 AM

What I have tried:-
I added a second range see below as the line I tried first (Set sourceRange = .Range("b8:b30, d8:t30") didn't work.

This works for missing out column C, can anyone help with the rows?

Please Login or Register  to view this content.

Set sourceRange = .Range("b8:b30")
Set sourceRange = .Range("d8:t30")

Please Login or Register  to view this content.

EDIT - Actually it didn't work, it completely misses out everything before Column D, damn!

**AB33** · 11-14-2013, 06:42 AM

I think the easiest and most efficient way would be to copy all relevant range, including the blanks in to summary( Master) sheet, then with 2-3 lines of a code, you can delete the blank rows from the summary sheet.

**Ginge1267** · 11-14-2013, 06:49 AM

Originally Posted by AB33

I think the easiest and most efficient way would be to copy all relevant range, including the blanks in to summary( Master) sheet, then with 2-3 lines of a code, you can delete the blank rows from the summary sheet.

Hi AB33, yes I agree, that's exactly what I'm trying to do now. I've added the code below but it's deleting rows I didn't expect it to. I am copying from other examples of similar requests and trying to understand it. Can you tell me please, what exactly does the ".SpecialCells(4).EntireRow.Delete" do?

Please Login or Register  to view this content.

**AB33** · 11-14-2013, 06:54 AM

It deletes if a cell in range c is blank. By blank, I mean not necessary a cell which has formula with blank string.
I have expanded the range you want to look for empty cells, so in this case, the code looks for empty columns in A-Z. If your range is fewer, you need to adjust it.
You are also replacing the error values with empty and delete them too.

Please Login or Register  to view this content.

**Ginge1267** · 11-14-2013, 07:05 AM

Originally Posted by AB33

It deletes if a cell in range c is blank. By blank, I mean not necessary a cell which has formula with blank string.
I have expanded the range you want to look for empty cells, so in this case, the code looks for empty columns in A-Z. If your range is fewer, you need to adjust it.
You are also replacing the error values with empty and delete them too.

Please Login or Register  to view this content.

Perfect, Yes I wanted the error values cleared, I've added a "." to the rows I don't want it to delete to column C, then as it deletes column C after deleting the 0 rows, the "." are cleared anyway and it doesn't delete the rows that I didn't want it to.

I'll try yours too to see how it effects.

Many Thanks for your help.

**AB33** · 11-14-2013, 07:08 AM

So, what is missing?

**Ginge1267** · 11-14-2013, 07:13 AM

As I had told it to delete all rows in column C that were blank or 0 after making the errors blank. Certain rows like where my total was didn't have anything in column C. By adding the "." to the rows I don't want deleted it now works perfectly.

Is that what you meant?

**AB33** · 11-14-2013, 07:20 AM

Is that what you meant?
What I want is you get the solution. That is what matters.

**Richard Buttrey** · 11-14-2013, 07:21 AM

Hi,

The simplest way I know of deleting unwanted rows is to set up a filter which displays only the unwanted rows then use a

Please Login or Register  to view this content.

syntax to delete the rows. This assumes row 1 contains the column headers which you don't want to delete

How can I ignore blank rows in this VB

LinkBack

Thread Tools

Rate This Thread

Display

How can I ignore blank rows in this VB

Re: How to ignore blank rows in this VBS?

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Re: How can I ignore blank rows in this VB

Thread Information

Users Browsing this Thread

Similar Threads

IF blank ignore

How can I make a macro ignore blank cells? - Blank Cells have formulas

A4 = A1 OR A2 OR A3 (Ignore blank)

Query + Ignore Empty/Blank Rows

[SOLVED] Ignore blank rows to populate custom drop down list column range

Bookmarks

Bookmarks

Posting Permissions