Using R to transform Excel files to be combined
I have not figured out all processing you need to do, so my solution will not give you the exact same data.frame, but it should be close enough for you to get by with minimal post-processing.

General consideration


You are using nested for loops to fill a data.frame. This is usually a bad idea, there are almost always better ways to go about it. If you really need to use loops, read about preallocation first (for example in this question on stackoverflow question)
Think about the structure of your data. Do you really need one row for each single individual, or is a count variable enough? (see below)
Your task can be split up into two distinct steps: Reshaping the input, and replicating the rows. A function should always do one thing and not several (though in this case I agree that it is not obvious that you are actually doing two things)


Reshaping the input

The first function restructures your data so that it is easier to deal with. We use the reshape2 package for that:

parse_chiro &lt;- function(x){
  taxa &lt;- names(x)[[1]]
  reshape2::melt(
    Chiro.1,
    id.vars = c("Habitat", taxa),
    variable.name = "Size",
    value.name = "count"
  )
}

res &lt;- parse_chiro(Chiro.1)


This gives you a data.frame with all the columns you want, + a count variable that contains how often you want the row to be replicated. The syntax for reshape is a bit hard to explain and I refer you to the package documentation for that. You can achieve something similar with the newer tidyr package, but I personally prefer reshape. 

Expanding the data

The next part is expanding the data.frame with the count variable. 
This is where most if not all of the speed improvement to your original function comes from. I cannot think of an application where you would want your data like that, but if you still need to do it, go about it like this:

expand_chiro &lt;- function(x){
  # Removes all rows with NA count. I am not sure if you really want that?
  x &lt;- x[!is.na(x$count), ]  
  x$rowid &lt;- seq_len(nrow(x))
  rep_rowid &lt;- rep(x$rowid, round(x$count))
  x[rep_rowid, ]
}


Using the vectorized function rep() and data.frame row indexing will yield a significant speed increases to your nested loops. There were also a few things wrong with your loop that could have improved the performance, but the way I propose is much cleaner, so I am not going into that. 

Remarks


rbind all your parsed data.frames before you do the expanding (so that you only need to expand once)
I would recommend to stick to the tidyverse style guide for naming your functions and variables. It is currently the most popular style guide for R, and you are already using tidyverse packages (readxl, dplyr)
2M rows are no problem in R, the limit is your RAM. You can use format(object.size(x), "auto") to display the size of your object in human readable form. In theory, you can work with objects about 1/3 - 1/2 the size of your available RAM, but if you are not super careful and know what you are doing you might crash R. Your data.frame should be only a few hundred MB so no need to worry.