VBA - Copy/Pasted range to shift up if there are empty cells in b/w

fullspeed

New Member
Joined
Sep 8, 2014
Messages
6
Hi, I just started learning VBA by watching Youtube videos and reading books. I can't figure out what the issues are with the below codes. what I am trying to accomplish is
1) Copy/Paste range of cells from sheet1 tab to a tab called "Output"

2) Once the data is copied, look through empty cells and shift up so there are no longer empty cells

But I am getting this error: " Compile error: Type Mismatch "
Maybe the order of the codes are wrong? do I need to declare variables before copy/paste? Also, I am not sure what the rules are for having separate sub routines?
Thank you very much for your help in advance!




Sub CopyPasteRange()


Worksheets("Output").Range("A1:C5").ClearContents
Worksheets("Sheet1").Range("A1:C5").Copy
Worksheets("Output").Range("A1:C5").PasteSpecial


End Sub


Sub ShiftValuesToTop()


Dim rei As String
Dim OPsheet As Worksheet
Set OPsheet = ThisWorkbook.Sheets("Output")


For rei = 1 To 3 'colums A to C
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
Next rei


MsgBox rei.Cells.Count


End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,242
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What line is highlighted when you get the compile error? The msgbox line will generate a run time error b/c rei is an integer having no cells property.
 

fullspeed

New Member
Joined
Sep 8, 2014
Messages
6
Hi Joe,
The line that's highlighted is this: Sub ShiftValuesToTop()

Thank you


 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,242
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Joe,
The line that's highlighted is this: Sub ShiftValuesToTop()

Thank you


I don't think that's the line that gets highlighted. It's probably this line:

For rei = 1 To 3 'colums A to C

because you have dimensioned rei as a string. Try dimensioning rei as a Long and get rid of the msgbox so that doesn't cause a run time error.
 

fullspeed

New Member
Joined
Sep 8, 2014
Messages
6
I don't think that's the line that gets highlighted. It's probably this line:

For rei = 1 To 3 'colums A to C

because you have dimensioned rei as a string. Try dimensioning rei as a Long and get rid of the msgbox so that doesn't cause a run time error.

Hi Joe,
I may be missing something completely.
the cells have texts and numbers. that's why I chose string instead of long.

I changed it to Long now, and run
CopyPasteRange - the first sub, it runs.
then when I run
ShiftValuesToTop, it says
run-time error '1004':
No cells were found.

when i click on debug, it highlights below part in yellow:
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,242
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Joe,
I may be missing something completely.
the cells have texts and numbers. that's why I chose string instead of long.

I changed it to Long now, and run
CopyPasteRange - the first sub, it runs.
then when I run
ShiftValuesToTop, it says
run-time error '1004':
No cells were found.

when i click on debug, it highlights below part in yellow:
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

The specialcells method generates an error if no specialcells meeting the criteria (in your case that's blank cells) are found in the designated range. You can avoid the error message by preceding the specialcells line with "On Error resume Next", but that doesn't eliminate the error just the message and code disruption.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,173
Members
409,854
Latest member
rickcoba
Top