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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,915
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,915
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,915
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,452
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top