Select range of variable size - problem

blaksnm

Well-known Member
Joined
Dec 15, 2009
Messages
545
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 :)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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
545
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,772
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
545
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,772
Glad it worked, but you're still unecessarily selecting cells
 
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,163,880
Messages
5,834,222
Members
430,265
Latest member
CDane

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