Menu
Ask a question Report

Select and copy only nonblank cells [Solved/Closed]

Posts
14
Registration date
Sunday February 6, 2011
Last seen
September 1, 2011
 - - Latest reply: Ambucias
Posts
50495
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
March 21, 2019
 - Feb 15, 2011 at 05:55 AM
Excel Ask a question Report
Hello,

many thanks in advance for any responses I get here, I have toiled in vain on this for hours now.

At first it was acceptable to simply copy all the rows in a worksheet and paste them into another workbook. (code attached)

Sub FileB9()

If Not IsEmpty(Range("B9").Value) Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim FN As String
FN = Range("b9")
Workbooks.Open Filename:=FN, UpdateLinks:=0
Sheets("TotalWeek").Visible = True
Sheets("TotalWeek").Select
Range("a3:bk65000").Copy Workbooks("workbook2.xls").Sheets("DataArchive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If

End Sub

As you can see, this code will look at cell b9 and determine a file path, open that file and copy all of the data in the "totalweek" tab. Finally it will paste this information to the first blank cell in the "dataarchive" tab and close the source workbook without saving changes or bothering the human user.

Now things have been expanded a little and the "DataArchive" would need to contain information from more than one source.

I'd be grateful to find out how to change my "Range("a3:bk65000").Copy" to say, "count how many rows from a3:a65000 have information in them (nonblank?), then select only that nuymber of rows"... and so on


Again, many thanks in advance :)

ShootingFish

See more 

3 replies

Report Respond to TrowaD
Posts
2451
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
March 21, 2019
 411
0
Thank you
Hi Shootingfish,

Is your data continuous?
Is it like:
A1: nonblank
A2: nonblank
A3: nonblank
A4: nonblank
or
A1: Blank
A2: nonblank
A3: Blank
A4: nonblank

In the first case you have already shown you know the answer in this part of your code: Cells(Rows.Count, 1).End(xlUp).

In the second case I can't help you, since I don't know how to handle that.

Best regards,
Trowa
Report
shootingfish
Posts
14
Registration date
Sunday February 6, 2011
Last seen
September 1, 2011
 1 -
Hi there, thanks for your response,

I can confirm that every cell in column A has data, it is the date. This would make it easier to create a macro since all other columns do have gaps. I have experimented with a macro to make all blank cells carry a value of "0" however, my problem becomes apparent again, I do not know the expression to use that would say "blank cells, nonblank cells" etc. and I also do not know much on how to specify the boundries of a macro (so all blank cells in the entire worksheet would have a "0" value, from A1:IV65000~)

At this point I must confess that "Cells(Rows.Count, 1).End(xlUp)" was a straight copy and paste from another post on this site - I don't know how to change it to suit my needs.
Report Respond to shootingfish
Posts
14
Registration date
Sunday February 6, 2011
Last seen
September 1, 2011
 1
0
Thank you
Fixed, I used the "usedrange" expression to copy a selection that is only as big as the used cells. This way the dataarchive sheet does not become full of data after just one file has been archived. The code below is followed by an exact copy, except the range("b8") becomes b9, where the next file name is and so on until all 20 of my workbooks have been opened, archived and closed again... all without me doing anything. I got this answer from investigating what the helpful comments were telling me, so thanks to all who commented.


Sub FileB8()

If Not IsEmpty(Range("B8").Value) Then
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim FN As String
FN = Range("b8")
Workbooks.Open Filename:=FN, ReadOnly:=True, UpdateLinks:=0
Sheets("Currentyear").Visible = True
Sheets("currentyear").Select
Range("A1:A2").Select
Range("A2").Activate
Selection.EntireRow.Delete
Range("A1").Select
Sheets("currentyear").UsedRange.Copy Workbooks("MasterArchiveV4.xls").Sheets("DataArchive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If

End Sub
Report
Ambucias
Posts
50495
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
March 21, 2019
 20719 -
Thanks to you for your feedback and solution. Have a good day.
Report Respond to venkat1926
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 930
-1
Thank you
if you want to count number of non blank cells use countA

see what this formula give

=countA(A1:A65000)
Ask a question