Delete Blank Cells

billfold

New Member
Joined
Apr 30, 2012
Messages
6
So, I need some help deleting blank cells, or adjusting my code to only copy cells in the range that have a value. Here are the steps:


  1. Copy Range B2:B2000 (which has formulas in it)
  2. Paste the Values of that Range into J2:J2000
  3. Then copy that range however many times the user wants it copied into one column. I was able to get this to work, but there are blanks copied over.

Also, this site always used to come up when I would search for some excel answers, but for some reason never pops up anymore.

Here is the code:

Code:
Sub copy()
Dim mR As Range


Worksheets("UserStoreList").Range("B2:B2000").Copy
Worksheets("UserStoreList").Range("J2:J2000").PasteSpecial xlPasteValues


Set mR = Worksheets("UserStoreList").Range("J2:J2000")
Dim HowManyTimes As Integer: HowManyTimes = Worksheets("US Form").Range("D52")


mR.Copy Worksheets("Sheet2").Range("A2").Offset(0).Resize(mR.Rows.Count * HowManyTimes)


Worksheets("UserStoreList").Range("J2:J2000").Delete


End Sub

Thank you for your help everyone.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,455
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
Sub billfold()
Dim mR As Range


With Worksheets("UserStoreList")
   .Range("J2:J2000").Value = .Range("B2:B2000").Value
   .Range("J2:J2000").SpecialCells(xlBlanks).Delete xlUp
   Set mR = .Range("J2", .Range("J" & Rows.Count).End(xlUp))
End With
Dim HowManyTimes As Integer: HowManyTimes = Worksheets("US Form").Range("D52")


mR.copy Worksheets("Sheet2").Range("A2").Offset(0).Resize(mR.Rows.Count * HowManyTimes)


Worksheets("UserStoreList").Range("J2:J2000").Delete


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,455
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,401
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top