Select range of variable size - problem

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
524
Office Version
  1. 365
Platform
  1. Windows
Hey guys
I have this dynamic range- starting in F7, number of rows will vary.
There should be no emty cells in the range.
Problem 1)
The range is generatet by copy/paste of a filtered range in a larger list.
When using this:
Range("F7").Select
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(0, 0).Resize(tbl.Rows.Count, 1).Select

- I expect the dynamic range to be selected, but this does not happend :(
The macro obviously find some values in a larger range.

Problem 2)
My aim is to create/paste a formula in all corresponding cells in range G7:Gn - just for the valued celles in range F7:Fn
<TABLE style="WIDTH: 101pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=135><COLGROUP><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 1177" width=23><COL style="WIDTH: 42pt" span=2 width=56><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 17pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=18 width=23></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 width=56>"F"</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 width=56>"G"</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>7 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SWL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>8 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-171</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>9 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-170</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>10 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-074</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>11 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-072</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>12 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-070</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=18>13 </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>X-SI-066</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66>Formula</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=18></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=18>n</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71 colSpan=2>Variatiojn of rows</TD></TR></TBODY></TABLE>

If i manage to select the right range, Ill manage to make the formula-stuff :)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
You don't need to select cells to write formulae to them...

Code:
Sub a()
Dim Last_Row As Long

Last_Row = Range("F" & Rows.Count).End(xlUp).Row

Range("G7:G" & Last_Row).Formula = "Enter formula here"


End Sub
 

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
524
Office Version
  1. 365
Platform
  1. Windows
Thanks :)
This solves one of the problems (see result below)
<TABLE style="WIDTH: 241pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=321><COLGROUP><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 150pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17 width=200>X-SWL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; WIDTH: 91pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=121>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-171</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-170</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-074</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-072</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-070</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-066</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-061</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-054</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-026</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-023</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-019</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-005</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=17>X-SI-001</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #d8d8d8; HEIGHT: 12.75pt; COLOR: red; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl64 height=17> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>Enter formula here</TD></TR></TBODY></TABLE>

The macro certainly read som mysterious hidden characters in the "emty" celles ... :(
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Are you sure they're empty? If you select F7 and press Ctrl+down arrow, which cell is selected?

This may be due to non-printable characters. In which case, you may need to amend the code to count how many cells in column F have a length greater than 1 (for example).
 

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
524
Office Version
  1. 365
Platform
  1. Windows
I have modified your macro:
Sub TEST2()
Dim Last_Row As Long
Last_Row = Range("F" & Rows.Count).End(xlUp).Row
Range("F8:F" & Last_Row).Select
'Range("G8:G" & Last_Row).Formula = "Enter formula here"
Selection.Replace What:="", Replacement:="0", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Last_Row = Range("F" & Rows.Count).End(xlUp).Row
Range("G8:G" & Last_Row).Formula = "Enter formula here"

End Sub

By replace of "nothing" with "zero" (as whole cell content) and than revers this action all fuctions all right ....

Your help pushed me in the right directon - thanks a lot :)
 
Last edited:

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Glad it worked, but you're still unecessarily selecting cells
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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