Related:
- Copy and paste only non-blank cells with vba code
- Copy non blank cells excel vba - Best answers
- Select non blank cells in excel vba - Best answers
- Macro copy and paste in next blank cell ✓ - Forum - Excel
- Vba copy data from a range and paste to empty column - Forum - Excel
- Macro for copy paste until blank ✓ - Forum - Programming
- Macro that goes to next blank cell ✓ - Forum - Programming
- Macro to find last blank cell in column ✓ - Forum - Excel
3 replies
TrowaD
- Posts
- 2690
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 12, 2021
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
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
shootingfish
- Posts
- 14
- Registration date
- Sunday February 6, 2011
- Status
- Member
- Last seen
- September 1, 2011
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
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
Subscribe To Our Newsletter!
- Posts
-
14
- Registration date
- Sunday February 6, 2011
- Status
- Member
- Last seen
- September 1, 2011
1I 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.