• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

Ignore blank rows

Hi,
Can someone please advise how I can input an if statement on vba that if a row contains no values or a spreadsheet contains no information that the code will ignore row. Here is the code I've gotten to combine information from multiple spread sheets but just need to put in an error statement or if statement in. Not sure which is needed

Thanks
Seamus

Sub Combine()
'
Dim NumSheets As Integer
Dim NumRows As Integer

' Change the value of NumSheets to equal the number of sheets you wish to combine

NumSheets = 10
' Change the value of NumRows to equal the number of rows in each sheet

NumRows = 500
Worksheets(12).Select
Sheets.Add
ActiveSheet.Name = "Consolidated"
For X = 1 To NumSheets
Worksheets(X + 1).Select
Rows("2:" & NumRows).Select
Selection.Copy
Worksheets("Consolidated").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Worksheets(X + 2).Select
Range("A1").Select
Next X
Worksheets("Consolidated").Select
Range("A1").Select
End Sub

Open in new window

0
Seamus2626
Asked:
Seamus2626
3 Solutions
 
David L. HansenProgrammer AnalystCommented:
What is the symptom you are experiencing (error, slow down, bad results)?
0
 
Seamus2626Author Commented:
To be honest im not really sure why my colleague has requested this. I think what may be suitable is if i set the range on both work books using ColA and then using that delete any blank rows inbetween and then copy on to the new workbook? Have you that code to Set the range and then delete blank rows inbetween?

Thanks
Seamus
0
 
Glenn RayExcel VBA DeveloperCommented:
Couple of observations:
1) If NumRows is intended to be the actual number of rows copies, you need to add one to any subsequent formulas using it because the source sheets are copied from row 2 to NumRows (line 17), leaving you one row short.
2) If a blank row exists in the source data, then the Selection.End(xlDown).Select statement (line 21) will not work because it will only move the active cell down as far as the first blank row in the copied data on the Consolidated worksheet.

So, am I correct in the assumption that if a blank row(s) exists in the source data sheets, those rows don't count toward the NumRows total?  For example, if there are 10 blank rows, you actually want to copy as far as the 510th row (eventually removing the blank rows from the copy process)?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AgeOfEmpiresCommented:
There are several approaches, IMO, but the optimal solution is dependent on the nature of your data.

The hardcoded 500 lines stands out as a potential troublemaker (as does the hard coded number of worksheets).  What happens if (when?) the data extends beyond 500 rows, there is a new user running the routine, and they forget to edit the code?  You get an inaccurate consolidated worksheet.

Therefore, I'd go about this a little differently, which would also accomplish ignoring blank rows on the source worksheets.

Essentially, for each source sheet, I'd loop through all the rows in the UsedRange of the worksheet (based upon how the spreadsheet has been maintained, this may involve WAY more rows than contain actual data, but adapting some of the following logic, you could build in a maintenance plan to get rid of any extra rows at the end).  While looping through the rows, for each row, check to see if A1 is blank.  If not, you need to copy the row.  If A1 is blank, do an end(xlToRight), and check that cell to see if it is blank.  If your entire row is blank, it will be.  If there is data any where in your row, the cell at end(xlToRight) will not be blank and you know you need to copy that row.

Now, as mentioned, this requires you to loop through each row on each source sheet, but given that your above parameters show 10 worksheets and 500 rows each, this methodology should not pose a real performance problem.  If you've got formulas in any of the cells, you could turn off auto calc, do all of the copying, and then reenable calc (assuming it was on when you started the copy).
0
 
ragnarok89Commented:
Seamus,

Assuming you are using 5 columns per row
Assuming a blank row is one where all 5 columns are blank

Load the entire range into an array. Then for each row, check if any columns are blank. If yes, move to the next row; if not then paste the row:
Sub Combine()
    
Dim NumSheets As Integer
Dim NumRows As Integer

' Change the value of NumSheets to equal the number of sheets you wish to combine

NumSheets = 10
' Change the value of NumCols to equal the number of columns in each sheet
NumCols = 5
' Change the value of NumRows to equal the number of rows in each sheet
NumRows = 500
Worksheets(12).Select
Sheets.Add
ActiveSheet.Name = "Consolidated"
For x = 1 To NumSheets
    Worksheets(x + 1).Select
    'put all rows into an array, this assumes Col E is last column
    temparray = Range("A2:E" & NumRows)
    Worksheets("Consolidated").Select
    'select last used row, and then the one below
    Range("A65536").End(xlUp).Offset(1, 0).Select
    'check each column of each row in the array
    For i = 1 To UBound(temparray)
        For j = 1 To NumCols
            'if a blank is found, goto next row in array
            If temparray(i, j) = "" Then GoTo blank:
        Next j
        'if no blanks, paste the row
        For j = 1 To NumCols
            ActiveCell.Offset(1 - i, 1 - j).Value = temparray(i, j)
        Next j
blank:
    Next i
    Worksheets(x + 2).Select
    Range("A1").Select
Next x
Worksheets("Consolidated").Select
Range("A1").Select
End Sub

Open in new window

0
 
Seamus2626Author Commented:
This question was a favour for a friend who wouldnt provide the data behind it, so im going to close it off

Thanks for all your help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now