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

[fullspeed]

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

 

[JoeMo]

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]

Hi Joe,
The line that's highlighted is this: Sub ShiftValuesToTop()

Thank you

 

[JoeMo]

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]

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]

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.