0

enter image description hereIm trying to construct a vba code which will clear content if cells = 0. However this depends on the previous vba code which changes the format of some cells. My code until now is:

Sub Macro1()

Application.DisplayAlerts = False

Dim lastRow As Variant

Range("A4:A65000").Select
Selection.TextToColumns Destination:=Range("B4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
    TrailingMinusNumbers:=True
Range("B4").Select

 Range("E4:F4").AutoFill Destination:=Range("E4:F" & Range("A" & Rows.Count).End(xlUp).Row)

 lastRow = Range("A" & Rows.Count).End(xlUp).Row


  lastRow = lastRow + 1
  Rows(lastRow & ":F" & 2 ^ 20).ClearContents


  End Sub

So depending on the code i sometimes end up with a alot of cells containg "00" in row "G".

I thought i could use the code "Clearcontent", but im unsure to use it when the cells are adjusting all the time.

1 Answers1

0
  1. Locate last row in the column A. Last row in column VBA Lets say it is row 250. Let's say it is k
  2. Then write the following at the end:
k = k + 1
Rows(k & ":" & 2^20).ClearContents
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • But the last row in column A is changing all the time, depending on how many dates I insert. –  Nov 20 '17 at 14:50
  • @Skyrup - that's why you should locate it with a VBA function, every time after you make an update - check the link in my answer. – Vityata Nov 20 '17 at 14:51
  • Im just using a regular code like this to find it: Range("A65536").End(xlup).Select. –  Nov 20 '17 at 14:55
  • What if you have data in cell A65537? You should find the last row programatically. – Fernando J. Rivera Nov 20 '17 at 14:56
  • Try this to find the last row in column A `lastRow = Cells(Rows.Count, "A").End(xlUp).Row` – Fernando J. Rivera Nov 20 '17 at 14:59
  • Allright, I see that would be a good idea. However im unsure how to write the code which will delete the data in Row "G" up to the last row in "A" which I have just found. –  Nov 20 '17 at 15:03
  • Try with the two lines in my answer, @Skyrup – Vityata Nov 20 '17 at 15:04
  • lastRow = lastRow + 1 Rows(lastRow & ":" & 2 ^ 20).ClearContents. I am very sorry that im unable to understand, but what does " 2^20" do for the code? –  Nov 20 '17 at 15:09
  • `2^20` is `1048576`, e.g. the last row in Excel since Excel 2010, @Skyrup – Vityata Nov 20 '17 at 15:18
  • I get the error "missmatch". I have updated my question with my new code. –  Nov 20 '17 at 15:24
  • @Skyrup - try this - `Rows(lastRow & ":" & 2 ^ 20).ClearContents` – Vityata Nov 20 '17 at 15:26
  • 1
    Perfect, that was exactly what im looking for. Thank you so much, and its so much quicker than constructing a loop which was my alternative. –  Nov 20 '17 at 15:29
  • @Skyrup - congrats! :) – Vityata Nov 20 '17 at 15:31