VBA for non blank cells

ral_ap

New Member
Joined
Apr 26, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hello. I have two sheets: sheet 1 -"stocks" and the other, 2- "template". I want to determine each non blank value (from column F with multiple rows) from sheet 1 and to put it in sheet number 2 (in cell A9 - till A+). Each non blank value must fill sheet number 2 "template" one by one, on the following empty row

example:
sheet 1 "stocks" - column F
1
(blank)
5
3
7
8
(blank)
4
5

Sheet 2 "template" (how it should look like)
1 (A9 - cell position)
5 (A10)
3 (A11)
7 (A12)
8 (A13)
4 (A14)
5 (A15)


Can anyone help me?
I have started with ----
If Not IsEmpty(Range("F"&i).Value) Then

End if

but i am blocked at putting the values one by one on the next row at sheet "template"
Thank you for your time
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
Please Upload your example file & Desired Results with XL2BB Addin (Preferable) or upload at free hosting site e.g. www.dropbox.com, GoogleDrive or OneDrive and Insert link here.
 

ral_ap

New Member
Joined
Apr 26, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
unfortunately i am limited at this computer in using certain programs, so i inserted images in order to make myself clearer..
hope it is ok

sheet 1.png
---sheet 1

resulting sheet -template.png
--- resulting sheet 2
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this Method:
1. Select all Values and Blank cells at column F
2. Press F5 key on KeyBoard
3. Select Special
4. Select Constants
5. Press OK.
6. Press CTRL+C on Keyboard for Copy
7. Go to destination sheet , AND elect Cell A9
8. Press CTRL+V on Keyboard

AND VBA
VBA Code:
Dim Lr as Long
Lr = Sheets("Stocks").Range("F" & Rows.Count).End(xlup).row
Sheets("Stocks").Range("F2:F" & Lr).SpecialCells(xlCellTypeConstants, 23).Copy  Sheets("Template").Range("A9")
 

ral_ap

New Member
Joined
Apr 26, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

i started creating a VBA formula but i got stuck
Thank you for your response
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I modify post check it.
 

ral_ap

New Member
Joined
Apr 26, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
uh . something's not working. the merged cells from the drawing is a problem. i'll return when i can understand what the problem is. Thank you
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

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