I need to reference a dynamic cell range in vba code

[senthia87]

How do i reference a cell range in vba when the range is dynamic?

Basically I have a table underneath rows of data, at anytime data can be inserted into those rows. So everytime data is inserted my table shifts down a row and the cell reference in my vba code no longer match up. Is there a way to fix this?

 

[James Snyder]

Is the row being shifted down in the macro, or by formula?

 

[senthia87]

There is a row inserted via macro when a button is clicked that is linked to the code. I can't put the table above the rows of data because there is stuff there that cannot be moved without messing with another system it has to go into.

 

[JackDanIce]

Depends on the position of the cell and how you identify it. Say you want the last used cell in a column but don't know what row it's on, you could use:

Range("A" & rows.count).end(xlup).select

This will select the last used cell in column A, regardless of how many rows are above it or if rows are inserted or deleted

 

[senthia87]

Not Current</SPAN>

Current</SPAN>

Not Current </SPAN>

Current</SPAN>

Not Current</SPAN>

Current</SPAN>

Not Current </SPAN>

Current</SPAN>

Not Current </SPAN>

Current</SPAN>

1</SPAN>

26</SPAN>

50</SPAN>

75</SPAN>

99</SPAN>

2</SPAN>

27</SPAN>

51</SPAN>

76</SPAN>

100</SPAN>

3</SPAN>

28</SPAN>

52</SPAN>

77</SPAN>

101</SPAN>

4</SPAN>

29</SPAN>

53</SPAN>

78</SPAN>

102</SPAN>

5</SPAN>

30</SPAN>

54</SPAN>

79</SPAN>

103</SPAN>

6</SPAN>

31</SPAN>

55</SPAN>

80</SPAN>

104</SPAN>

7</SPAN>

32</SPAN>

56</SPAN>

81</SPAN>

105</SPAN>

8</SPAN>

33</SPAN>

57</SPAN>

82</SPAN>

106</SPAN>

9</SPAN>

34</SPAN>

58</SPAN>

83</SPAN>

107</SPAN>

10</SPAN>

9</SPAN>

9</SPAN>

35</SPAN>

59</SPAN>

84</SPAN>

108</SPAN>

11</SPAN>

36</SPAN>

60</SPAN>

85</SPAN>

12</SPAN>

37</SPAN>

61</SPAN>

86</SPAN>

13</SPAN>

38</SPAN>

62</SPAN>

87</SPAN>

14</SPAN>

39</SPAN>

63</SPAN>

88</SPAN>

15</SPAN>

40</SPAN>

64</SPAN>

89</SPAN>

16</SPAN>

41</SPAN>

65</SPAN>

90</SPAN>

17</SPAN>

42</SPAN>

66</SPAN>

91</SPAN>

18</SPAN>

43</SPAN>

67</SPAN>

92</SPAN>

19</SPAN>

44</SPAN>

68</SPAN>

93</SPAN>

20</SPAN>

45</SPAN>

69</SPAN>

94</SPAN>

21</SPAN>

46</SPAN>

70</SPAN>

95</SPAN>

22</SPAN>

47</SPAN>

8</SPAN>

8</SPAN>

71</SPAN>

96</SPAN>

23</SPAN>

48</SPAN>

72</SPAN>

97</SPAN>

24</SPAN>

49</SPAN>

73</SPAN>

98</SPAN>

25</SPAN>

74</SPAN>

<TBODY>
</TBODY>

The range of the table is from A36:O61. I want to reference the noncurrent cell for each number in my code.

 

[James Snyder]

Using his code above:

Dim rowA As Long
Dim rowB As Long
Dim currentA As String
Dim currentB As String
Dim refA As Range
Dim refB As Range
currentA = "B"
currentB = "D"
rowA = Range(currentA & rows.count).end(xlup).row
rowB = Range(currentB & rows.count).end(xlup).row
refA = .Range(currentA & rowA)
refB = .Range(currentB & rowB)

 

[senthia87]

I add the first code and then the one that was just posted?


This is the code i have right now. The bolded cells are the ones that need to be referenced even though the rows might shift. I wish i could attach a sample spreadsheet.

Private Sub Workbook_BeforeClose(cancel As Boolean)
'Run macro to last row of data
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'Assign variables to individual cases
For i = 2 To finalrow
Select Case Cells(i, 40).Value
Case "ABC1"
Cells(i, 41) = 1
Case "DEF1"
Cells(i, 45) = 2
Case Else
End Select
Next i
'ABC
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 33 To finalrow
If Cells(i, 43).Value > [B47] Then
Cells(i, 44).Value = "YES"
ElseIf Cells(i, 44).Value <= [B47] Then
Cells(i, 44).Value = " "
End If
Next
'DEF
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 33 To finalrow
If Cells(i, 47).Value > [E59] Then
Cells(i, 48).Value = "YES"
ElseIf Cells(i, 47) <= [E59] Then
Cells(i, 48).Value = " "
End If
Next
End Sub

I appreciate your help.